format or template function?

[Stuart McGraw]

I am looking for a VBA "format" or "template" function, that is,
a function that takes a format string and a varying number of
arguments, and substitutes the argument values into the format
string as specified in the format string. For example
fmt("this is $1 format string. arg2 is $2", "short", 3)
would return the string
"this is short format string. arg2 is 3"

Now, the above is easy to write and I have done so. What I want
is something similar that also has iterating and conditional abilities.
For example if 'rs' is a recordset (or array variable) with 3 records
and the "iterate" format spec is "<$", "$>" then
fmt ("the members are $< $1!name($1!age),$>", rs)
would return
"the members are smith(23), wilson(37), dobbs(18) "

Of course I don't care much about the exact syntax used in
the format string, just that it has these capabilities in general.

Anyone know of anything like this (preferably free)? Several
hours of web searching did not turn up anything. And yes, this
is an Access question (sort of :-), at least in the sense that I
will be using this in an Access application.

[Tom van Stiphout]

On Mon, 7 Mar 2005 08:23:22 -0700, "Stuart McGraw"
<sm********@zzfrii.remoovAllZZs.com> wrote:

There is the Windows API function wsprintf, but you may have a hard
time calling a "C calling convention" function from VBA.

-Tom.

I am looking for a VBA "format" or "template" function, that is,
a function that takes a format string and a varying number of
arguments, and substitutes the argument values into the format
string as specified in the format string. For example
fmt("this is $1 format string. arg2 is $2", "short", 3)
would return the string
"this is short format string. arg2 is 3"

Now, the above is easy to write and I have done so. What I want
is something similar that also has iterating and conditional abilities.
For example if 'rs' is a recordset (or array variable) with 3 records
and the "iterate" format spec is "<$", "$>" then
fmt ("the members are $< $1!name($1!age),$>", rs)
would return
"the members are smith(23), wilson(37), dobbs(18) "

Of course I don't care much about the exact syntax used in
the format string, just that it has these capabilities in general.

Anyone know of anything like this (preferably free)? Several
hours of web searching did not turn up anything. And yes, this
is an Access question (sort of :-), at least in the sense that I
will be using this in an Access application.

[Stuart McGraw]

Thanks Tom, but besides the calling problems, wsprintf() does
only single variable subsitution, it does not do the iterative and
conditional template-style substitutions i need.

I can find functions that will do what I'm asking for in every other
modern language I've looked at (python, perl, java, c/c++, ruby)
and i'll bet if i looked hard, I could find one written in fortran!!
Why not VB??!!

"Tom van Stiphout" <no*************@cox.net> wrote in message news:gi********************************@4ax.com...

On Mon, 7 Mar 2005 08:23:22 -0700, "Stuart McGraw"
<sm********@zzfrii.remoovAllZZs.com> wrote:

There is the Windows API function wsprintf, but you may have a hard
time calling a "C calling convention" function from VBA.

-Tom.

I am looking for a VBA "format" or "template" function, that is,
a function that takes a format string and a varying number of
arguments, and substitutes the argument values into the format
string as specified in the format string. For example
fmt("this is $1 format string. arg2 is $2", "short", 3)
would return the string
"this is short format string. arg2 is 3"

Now, the above is easy to write and I have done so. What I want
is something similar that also has iterating and conditional abilities.
For example if 'rs' is a recordset (or array variable) with 3 records
and the "iterate" format spec is "<$", "$>" then
fmt ("the members are $< $1!name($1!age),$>", rs)
would return
"the members are smith(23), wilson(37), dobbs(18) "

Of course I don't care much about the exact syntax used in
the format string, just that it has these capabilities in general.

Anyone know of anything like this (preferably free)? Several
hours of web searching did not turn up anything. And yes, this
is an Access question (sort of :-), at least in the sense that I
will be using this in an Access application.

[DFS]

Stuart McGraw wrote:

I am looking for a VBA "format" or "template" function, that is,
a function that takes a format string and a varying number of
arguments, and substitutes the argument values into the format
string as specified in the format string. For example
fmt("this is $1 format string. arg2 is $2", "short", 3)
would return the string
"this is short format string. arg2 is 3"

Now, the above is easy to write and I have done so. What I want
is something similar that also has iterating and conditional
abilities. For example if 'rs' is a recordset (or array variable)
with 3 records and the "iterate" format spec is "<$", "$>" then
fmt ("the members are $< $1!name($1!age),$>", rs)
would return
"the members are smith(23), wilson(37), dobbs(18) "

Of course I don't care much about the exact syntax used in
the format string, just that it has these capabilities in general.

Anyone know of anything like this (preferably free)? Several
hours of web searching did not turn up anything. And yes, this
is an Access question (sort of :-), at least in the sense that I
will be using this in an Access application.

Stuart,

Since you specified the functionality so well, and you've written your own
functions, I'm wondering why you're having a difficult time writing this
one?

This may be what you're looking for:
Public Function fmt(funcType as String, rs as Recordset) as String

fmt = ""
if funcType = "iterate" then
if rs.recordcount = 0 then
fmt = "no members found"
else
do until rs.eof
fmt = fmt & rs("name") & "(" & rs("Age") & "), "
rs.movenext
loop
fmt = Trim(fmt)
fmt = "the members are " & left(fmt,len(fmt)-1)
endif
endif

End Function

[Tom van Stiphout]

On Tue, 8 Mar 2005 11:59:41 -0700, "Stuart McGraw"
<sm********@zzfrii.remoovAllZZs.com> wrote:

I don't agree wsprintf doesn't support multiple substitutions. but it
probably can't be used for other reasons anyway. I now seem to
remember you cannot call cdecl API functions from VBA period.

If you embark on writing your own, look into ParamArray option of the
Function statement. It is VBA's way to support cdecl calling
convention.
I also like the Array function, because it allows you to put as many
elements as you need into a single variant. In our company we have
written a code library that extensively uses Array(Array(...)) and
passes it around as a single variant.

If you write your own and have to decide on a syntax, try sticking
closely to something that already exists. Think regular expressions,
or the name/value pairs in a querystring, or HTML as examples of
standard syntax that perhaps could be used.

-Tom.

Thanks Tom, but besides the calling problems, wsprintf() does
only single variable subsitution, it does not do the iterative and
conditional template-style substitutions i need.

I can find functions that will do what I'm asking for in every other
modern language I've looked at (python, perl, java, c/c++, ruby)
and i'll bet if i looked hard, I could find one written in fortran!!
Why not VB??!!

"Tom van Stiphout" <no*************@cox.net> wrote in message news:gi********************************@4ax.com...

On Mon, 7 Mar 2005 08:23:22 -0700, "Stuart McGraw"
<sm********@zzfrii.remoovAllZZs.com> wrote:

There is the Windows API function wsprintf, but you may have a hard
time calling a "C calling convention" function from VBA.

-Tom.

>I am looking for a VBA "format" or "template" function, that is,
>a function that takes a format string and a varying number of
>arguments, and substitutes the argument values into the format
>string as specified in the format string. For example
> fmt("this is $1 format string. arg2 is $2", "short", 3)
>would return the string
> "this is short format string. arg2 is 3"
>
>Now, the above is easy to write and I have done so. What I want
>is something similar that also has iterating and conditional abilities.
>For example if 'rs' is a recordset (or array variable) with 3 records
>and the "iterate" format spec is "<$", "$>" then
> fmt ("the members are $< $1!name($1!age),$>", rs)
>would return
> "the members are smith(23), wilson(37), dobbs(18) "
>
>Of course I don't care much about the exact syntax used in
>the format string, just that it has these capabilities in general.
>
>Anyone know of anything like this (preferably free)? Several
>hours of web searching did not turn up anything. And yes, this
>is an Access question (sort of :-), at least in the sense that I
>will be using this in an Access application.

[Stuart McGraw]

Thanks again Tom.

I don't agree wsprintf doesn't support multiple substitutions. Sorry, I should have chosen my words more carefully. I meant
a single format specifier being applied to a multivalued argument
(like an array or recordset) and expanding into a multiple strings,
one for each element of the argument, not, as I think you (justifiably)
took it, multiple format specifiers applied respectively to multiple
arguments.

It looks like I will have to write something myself. Yes, I will
look into some kind of HTML'ish or XML'ish syntax -- hopefully
I can find some VB code that will aid in parsing it. To date, I have
been using an extended printf-style syntax but things are becoming
to complicated to push that much further. I have discovered
that doing even simple parsing in VB is not much fun.
I am still a little surprised that this kind of thing has not already
been written for, for example, generating html pages from
templates. I guess with ASP and VBscript et.al. there is not
much need.

"Tom van Stiphout" <no*************@cox.net> wrote in message news:32********************************@4ax.com... On Tue, 8 Mar 2005 11:59:41 -0700, "Stuart McGraw"
<sm********@zzfrii.remoovAllZZs.com> wrote:

I don't agree wsprintf doesn't support multiple substitutions. but it
probably can't be used for other reasons anyway. I now seem to
remember you cannot call cdecl API functions from VBA period.

If you embark on writing your own, look into ParamArray option of the
Function statement. It is VBA's way to support cdecl calling
convention.
I also like the Array function, because it allows you to put as many
elements as you need into a single variant. In our company we have
written a code library that extensively uses Array(Array(...)) and
passes it around as a single variant.

If you write your own and have to decide on a syntax, try sticking
closely to something that already exists. Think regular expressions,
or the name/value pairs in a querystring, or HTML as examples of
standard syntax that perhaps could be used.

-Tom.

Thanks Tom, but besides the calling problems, wsprintf() does
only single variable subsitution, it does not do the iterative and
conditional template-style substitutions i need.

I can find functions that will do what I'm asking for in every other
modern language I've looked at (python, perl, java, c/c++, ruby)
and i'll bet if i looked hard, I could find one written in fortran!!
Why not VB??!!

"Tom van Stiphout" <no*************@cox.net> wrote in message news:gi********************************@4ax.com...

On Mon, 7 Mar 2005 08:23:22 -0700, "Stuart McGraw"
<sm********@zzfrii.remoovAllZZs.com> wrote:

There is the Windows API function wsprintf, but you may have a hard
time calling a "C calling convention" function from VBA.

-Tom.
>I am looking for a VBA "format" or "template" function, that is,
>a function that takes a format string and a varying number of
>arguments, and substitutes the argument values into the format
>string as specified in the format string. For example
> fmt("this is $1 format string. arg2 is $2", "short", 3)
>would return the string
> "this is short format string. arg2 is 3"
>
>Now, the above is easy to write and I have done so. What I want
>is something similar that also has iterating and conditional abilities.
>For example if 'rs' is a recordset (or array variable) with 3 records
>and the "iterate" format spec is "<$", "$>" then
> fmt ("the members are $< $1!name($1!age),$>", rs)
>would return
> "the members are smith(23), wilson(37), dobbs(18) "
>
>Of course I don't care much about the exact syntax used in
>the format string, just that it has these capabilities in general.
>
>Anyone know of anything like this (preferably free)? Several
>hours of web searching did not turn up anything. And yes, this
>is an Access question (sort of :-), at least in the sense that I
>will be using this in an Access application.

[Stuart McGraw]

"DFS" <no****@DFS.com> wrote in message news:qM*******************@fe07.lga...

Stuart McGraw wrote:

I am looking for a VBA "format" or "template" function, that is,
a function that takes a format string and a varying number of
arguments, and substitutes the argument values into the format
string as specified in the format string. For example
fmt("this is $1 format string. arg2 is $2", "short", 3)
would return the string
"this is short format string. arg2 is 3"

Now, the above is easy to write and I have done so. What I want
is something similar that also has iterating and conditional
abilities. For example if 'rs' is a recordset (or array variable)
with 3 records and the "iterate" format spec is "<$", "$>" then
fmt ("the members are $< $1!name($1!age),$>", rs)
would return
"the members are smith(23), wilson(37), dobbs(18) "

Of course I don't care much about the exact syntax used in
the format string, just that it has these capabilities in general.

Anyone know of anything like this (preferably free)? Several
hours of web searching did not turn up anything. And yes, this
is an Access question (sort of :-), at least in the sense that I
will be using this in an Access application.

Stuart,

Since you specified the functionality so well, and you've written your own
functions, I'm wondering why you're having a difficult time writing this
one?

This may be what you're looking for:
Public Function fmt(funcType as String, rs as Recordset) as String

fmt = ""
if funcType = "iterate" then
if rs.recordcount = 0 then
fmt = "no members found"
else
do until rs.eof
fmt = fmt & rs("name") & "(" & rs("Age") & "), "
rs.movenext
loop
fmt = Trim(fmt)
fmt = "the members are " & left(fmt,len(fmt)-1)
endif
endif

End Function

Thanks for your posting. But I'm afraid I wasn't clear enough.
Your function will indeed generate the output I want but it will
generate *only* that output. To change the output I have to
change the function. What I want is a function that doesn't
need to be changed but instead generates it's output by
substituting values into place holders in a format string that
is an argument to the function. So to change the output
string, I just change the format string argument, not the code.

Specifically, I need to generate (small) RTF formatted documents
to be displayed in an RTF ActiveX control. There are many
slightly different styles of document. Each document style has
several places in it where data will be substituted. In some places
I need to display tables or lists, where the amount of data
is variable. In other places the data displayed may depend
on some other piece of data. Writing a a dozen or more
similar separate functions, with code that intermixes VB and
little snippets of rtf and the data, will be a maintenance nightmare.
(I know because that is what I am doing now! :-(

Instead, I want all of these styles stored as template strings in
an Access table. To display one, I will retrieve it, give it to the
template formatter routine (the thing I am looking for!), along
with the data to be inserted into it, and write the output to the
RTF control. I can change the format of a document by changing
the RTF in the database, with no changes to code.