Generlizing Parsing function

[timothyl]

Hello, I have a parsing function which parse's with respect to a comma. If I go in to the module and change where the comma is declared and change it to a . then it will parse w.r.t a period similarly for a space or tab. So know I have three modules to parse three different ways. This is for a comma

'Create query like so, to use
'Toronto, Ontario, Canada Boston
'Field: City: GetCSWord([Location],1)
'Show: True
'Field: Region: GetCSWord([Location],2)
'Show: True
'Field: Country: GetCSWord([Location],3)
'Show: True

Function CountCSWords(ByVal S) As Integer
' Counts the words in a string that are separated by commas.

Dim WC As Integer, Pos As Integer
If VarType(S) <> 8 Or Len(S) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
Pos = InStr(S, ",")
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, S, ",")
Loop
CountCSWords = WC
End Function

Function GetCSWord(ByVal S, Indx As Integer)
' Returns the nth word in a specific field.

Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
WC = CountCSWords(S)
If Indx < 1 Or Indx > WC Then
GetCSWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, S, ",") + 1
Next Count
EPos = InStr(SPos, S, ",") - 1
If EPos <= 0 Then EPos = Len(S)
GetCSWord = Trim(Mid(S, SPos, EPos - SPos + 1))
End Function



Is there a way to Make a single module and declare in calling the function which way to parse. I have tried


Function CountCSWords3(ByVal S, c As String) As Integer
' Counts the words in a string that are separated by commas.

Dim WC As Integer, Pos As Integer
If VarType(S) <> 8 Or Len(S) = 0 Then
CountCSWords3 = 0
Exit Function
End If
WC = 1
Pos = InStr(S, "c")
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, S, "c")
Loop
CountCSWords3 = WC
End Function

Function GetCSWord3(ByVal S, c As String, Indx As Integer)
' Returns the nth word in a specific field.

Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
WC = CountCSWords3(S)
If Indx < 1 Or Indx > WC Then
GetCSWord3 = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, S, "c") + 1
Next Count
EPos = InStr(SPos, S, "c") - 1
If EPos <= 0 Then EPos = Len(S)
GetCSWord3 = Trim(Mid(S, SPos, EPos - SPos + 1))
End Function
It did not work get ambiguous name in query expression, Thank's

 

[wazz]

there are a couple of good string manipulators in the code repository forum if you want to have a look there, but i really don't recall if they do what you're asking.

there's also a thread or two around here about mimicking overloaded functions; search using the keyword overload or overloading and you should get a hit or two.

one approach you could take is to put in Optional parameters and have the function respond differently depending on which parameter(s) is/are supplied.

 

[MarkK]

In this situation you can use the VBA.Strings.Split() function. Pass in your text and a delimiter and it returns a variant array of strings. Use UBound() + 1 to count elements and reference any of them using an index. Sample code...

[SIZE="2"]Function GetCount(Text As String, Delimiter As String) As Integer
   Dim var
[COLOR="Green"]   'splits the text into variant array of strings at delimiters[/COLOR]
   var = Split(Text, Delimiter)
[COLOR="Green"]   'returns a count of the items in the array[/COLOR]
   GetCount = UBound(var) + 1
End Function

Function GetItem(Text As String, Delimiter As String, index As Integer) As String
   Dim var
[COLOR="Green"]   'splits the text into variant array of strings at delimiter[/COLOR]
   var = Split(Text, Delimiter)
[COLOR="Green"]   'returns the item at position 'index'[/COLOR]
   GetItem = var(index)
End Function[/SIZE]

But notice how this requires you to run the split twice if you want the count AND the item? This is a perfect opportunity to use a class module, which retains the value of the original split for the lifetime of the object, and you can reference the properties you need when you need them. You'd create and use the class like this...

Dim cdi as new cDelimitedItems
cdi.Init "This, is, a, test", ", "  [COLOR="Green"]'delimiter can be more than one char[/COLOR]
debug.print cdi.count, cdi.item(2)

[COLOR="Green"]'output will be ...
4     a
[/COLOR]

And the class module, named cDelimitedItems, contains this code...

Public Items As Variant

Property Get Item(index As Integer) As String
   Item = Items(index)
End Property

Property Get Count() As Integer
   Count = UBound(Items) + 1
End Property

Public Sub Init(Text As String, Delimiter As String)
   Items = Split(Text, Delimiter)
End Function

 

[wazz]

beautiful, as always.

one question: does Items need to be public?

 

[timothyl]

Wow, that was why more then I was hoping for, I am going to have to study it (I have only been at this for about 5 months, indeed I wrote my first function 2 days ago

Function ClearAll(c As String, f As Form)
Dim ctl As Control
For Each ctl In f.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If ctl.ControlSource = "" And ctl.Tag <> "c" Then
ctl.Value = Null
End If
Case Else
End Select
Next ctl
End Function

with a bunch of help from the form and was hoping I could just pass the delimiter with something like what I tried: c As String (which only shows my ignorance) as this might have been with in my skill level ). Having said all that I pasted your code in the (I hope) proper places and tried using it with mixed success(actually I am not really sure if its meant to be run but is rather a foundation to be expanded upon(again a declaration of my ignorance)). If it is suppose to be run I keep getting a run time error of 9 and it refuses to parse the first string element. It gives me something like this: abc, def, hij becomes
abc, def, hij def. Even with out being able to understand it, I can tell your code is indeed beautiful. Thank you both.

 

[MarkK]

No, 'Items' can be private for sure, but at its core this class is a list of things, almost like a collection. In a production environment I'd expect to be able to enumerate that list using a For...Each...Next loop.

dim cdi as new cDelimitedItems
dim var
cdi.Init "This is a test", " "
for each var in cdi.Items
  debug.print var
next

 

[MarkK]

Tim, use these functions. They're simpler...

[SIZE="1"]Function GetCount(Text As String, Delimiter As String) As Integer
   Dim var
   'splits the text into variant array of strings at delimiters
   var = Split(Text, Delimiter)
   'returns a count of the items in the array
   GetCount = UBound(var) + 1
End Function

Function GetItem(Text As String, Delimiter As String, index As Integer) As String
   Dim var
   'splits the text into variant array of strings at delimiter
   var = Split(Text, Delimiter)
   'returns the item at position 'index'
   GetItem = var(index)
End Function[/SIZE]

 

[wazz]

No, 'Items' can be private for sure, ... In a production environment I'd expect to be able to enumerate that list using a For...Each...Next loop.

ah, i see. it would be handy to have that publicly available. ty.

 

[timothyl]

lagbolt,thanks again for responding, your code works well and gives me lots of food for thought. Tim