VBA Function Instr?

DianeG63

Registered User.
Local time
Yesterday, 20:21
Joined
May 13, 2008
Messages
12
Hello, I have not coded in sometime, but need to find how many occurences of 1 character happen within one field.
Example: The contents of Field1 = V20.2, 786.50, 250.02, V06.8 Is there a function that I could use to tell me that there are 3 ',' in that field. Something like VBAFunction(1, rst!field1,","). Any help is greatly appreciated. Thanks in advance,
 
Something like this, maybe.

Code:
Dim c As Long
Dim i As Long

i = 1
Do
  i = InStr(Mid$(strTest,i), ",")
  If i Then
    c = c + 1
  End If
Loop Until i = 0
Debug.Print "Number of occurances was: " & c
(untested)
 
How about an even shorter one (and it has been tested):
Code:
Function HowMany(strInput As String, strDelimiter As String) As Long
    Dim varSplit As Variant
 
    varSplit = Split(strInput, strDelimiter, , vbTextCompare)
 
    HowMany = UBound(varSplit)
End Function
 
Last edited:
Thanks Banana and Bob, I placed the function and it works great!
 
BTW: you can cut it down to one line:

Code:
HowMany = Ubound(Split(strInput, strDelimiter, , vbTextCompare))

But that may be a bit too cryptic...
 
Lagbolt, that would be appropriate if we wanted count of elements but I think we wanted count of occurrences (e.g. the delimiter).
 
For example, I could pass:

HowMany "23,33,254,52,43,243", "3"

and the result will be: 5

which is correct:

23,33,254,52,43,243
 
OK here goes , head above the parapit as I realise you guys are vastly more experienced and knowledgeable than I, but doesn't Ubound give the highest
subscript, and isn't that 1 more than the number of delimiters?

Brian
 
OK here goes , head above the parapit as I realise you guys are vastly more experienced and knowledgeable than I, but doesn't Ubound give the highest
subscript, and isn't that 1 more than the number of delimiters?

Brian

UBound is zero based so taking that exactly gives the correct number. :)
 
UBound is zero based so taking that exactly gives the correct number. :)

That's what I thought, then I tried it and it didn't work, so I read help and it said option base only affects Lbound, I thought how can it so I tried with both options and Ubound gave the same result.
My tests must be flawed.
Will try again.

Brian
 
How have you been testing (what string and what delimter)? I tested many different scenarios and it always came out correct.
 
I ran a test on a DB I had with a delimiter of ; and it doesn't work, but I knocked together a new table with delimiter , and no problem.

I wont loose any sleep over it but might have another shot sometime but must go take care of the wife now.

Keep posting bob

brian
 
The thing to remember is that the delimiter is what you are really wanting to search for.

I tried this:

?HowMany(";;;;;;;;;;;;;;;;",";")
16

And this:

?HowMany("56;265;675;225;",";")
4


And as shown (those were pasted directly from the Immediate window, including the result.
 
:o :o :o

I apologise for wasting everybody's time but in my defence I do have lousy eyesight.

My original string and : and ; in it and need I say more I miss counted the ;

Brian
 
No worries, I was having viewing issues yesterday, so I completely understand. :)
 

Users who are viewing this thread

Back
Top Bottom