format or template function?

  • Thread starter Thread starter Stuart McGraw
  • Start date Start date
S

Stuart McGraw

I asked this in an different MS Access newsgroup without much
success so I thought I would try here.

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.
 
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.
Click to expand...

Here is an off-the-top-of-the-head function that may do what you want
(for arrays at least; I am not a database program, so I can't speak to
its applicability to record sets). It is only a model of an idea, so
feel free to tear it down and rebuild it as necessary. I used $/ to
delimit the "repeating" text (that is, $/ appears on both the right and
left of the text that has the repeat string in it) and $? for the items
to be filled in. The array is a one-dimensional array with the items in
proper order. The number of $? must evenly divide the number of elements
in the array. There is no error checking provided (it is your job to add
that<g>). Here is the function

Function FormatArray(Text As String, Items() As String) As String
Dim X As Long
Dim NumOfItems As Long
Dim HowManyFields As Long
Dim TextParts() As String
Dim ReplacementString As String
NumOfItems = UBound(Items) - LBound(Items) + 1
HowManyFields = UBound(Split(Text, "$?"))
TextParts = Split(Text, "$/")
ReplacementString = TextParts(1)
TextParts(1) = Replace(String$(NumOfItems / HowManyFields, _
Chr$(1)), Chr$(1), ReplacementString)
FormatArray = Join(TextParts, "")
For X = LBound(Items) To UBound(Items)
FormatArray = Replace(FormatArray, "$?", Items(X), , 1)
Next
End Function

and here is a sample of it being used (from the Click event of a
CommandButton).

Private Sub Command1_Click()
Dim MyItems() As String
ReDim MyItems(5)
MyItems(0) = "smith"
MyItems(1) = "23"
MyItems(2) = "wilson"
MyItems(3) = "37"
MyItems(4) = "dobbs"
MyItems(5) = "18"
MsgBox FormatArray("The members are $/$?($?), $/", MyItems)
End Sub

Hope you can make heads or tales of what is going on.

Rick - MVP
 
Hi Rick. Thanks for the code. I have not had any luck finding
a completely done solution (though I find it difficult to believe
that this hasn't been written many times before), so I will use
your code as a good starting point.
 
Back
Top