search for a string within a string variable

[Andre]

Hello,

Thank you for the help the last time I asked questions, they were very
helpful. I have another question. I'm now able to grab a string from
the user input test formfield. I now need to search for a string
within that variable to determine the next course of action. The
following is my code, but everytime I try to use more than one
character (right now I'm searching for one character, which I
deliberately insert), it barfs some error message at me. Right now
the code is behaving unpredictably, and I believe there should be a
different function for searching for a string within a string. Also,
when I used to do programming in Pascal, for more than a few
if...then...elses, it was better to use CASE. Is it the same with
VBA? Thank you very much.

Andre Susanto

 

[Jay Freedman]

Hi, Andre,

I think you forgot to paste your code into your post. Anyway, the answer
is... use the InStr() function.

nPos = InStr(strBigStringToSearch, strFindMe)

will look in the string strBigStringToSearch for the pattern strFindMe, and
return the integer position of the beginning of the match in nPos. If there
is no match, it returns 0.

For three or more possibilities, it is indeed more efficient to use Select
Case (the VBA syntax corresponding to the Pascal CASE):

Select Case aValue
Case "a"
' do "a" stuff
Case "b"
' do "b" stuff
...
Case Else
' catchall stuff
End Case

Look up both of these topics in the VBA help for more information.

 

[Jay Freedman]

Hi, Andre,

That code does look OK. To diagnose it, put a break point on the statement
before the first If line (left-click once in the vertical gray bar to the
left of the code window to put a red dot there). When execution reaches that
line, the code will stop and display the code window. Repeatedly press the
F8 key to step the code one statement at a time, and see which assignment
executes. Hover the mouse over the names of the variables to see their
values, or add them to the Watch window (right-click a name and select "Add
Watch").

You're correct about the way multi-character search patterns work. Again,
try single-stepping through that code to see what it's doing.

However, I think you'll do better with the Select Case structure. Since you
know exactly what strings are in the dropdown's list, you just need a Case
for each string. For the example you showed, it would be

Select Case BeltType
Case "9"
BeltTypePrce = 0.75
Case "8"
BeltTypePrce = 0.825
Case "7"
BeltTypePrce = 0.775
Case "6"
BeltTypePrce = 0.875
Case Else ' should never get here
MsgBox "Invalid BeltType"
End Select

 

[Perry]

Here's another one:

Function GetPrice(ByVal BeltType As String) As Double
GetPrice = Switch(BeltType = "9", 0.75, BeltType = "8", 0.825, _
BeltType = "7", 0.775, BeltType = "6", 0.875)
End Function

Immediate window check:
? getprice(9)
0,75

? getprice(6)
0,875

? getprice(7)
0,775

Krgrds,
Perry