Parse Help

Topic

New Reply

Hey Guys,

I need help with this, what I am trying to do is extract a value from a string, in the string there is (35), or could be a any number within brackets, which I need for a calculation, can I use the “(” and “)” as I have in the formula?

Thanks in advance,
Darryl.

Option Explicit

dim weekvalue as double

weekvalue = VBA.Mid(Mail_Form.ListBox1.Value & “+”, VBA.InStr(1, “(“, Mail_Form.ListBox1.Value & “+”, vbTextCompare), VBA.InStr(1, “)”, Mail_Form.ListBox1.Value & “+”, vbTextCompare))

Reply | Quote

Replies

Darryl,

Try this:

Code:

Option Explicit

Sub Extract()

   Dim iWeekValue    As Long 'Use this unless you have a reason for Double!
   Dim zListBoxValue As String
   Dim lStart        As Long
   Dim lEnd          As Long
   
'   zListBoxValue = Mail_Form.ListBox1.Value  'For effieiency - Uncomment for use

    zListBoxValue = "xyz6789123(35)hello there"  'For Testing only! - Delete or comment for use
   
   lStart = InStr(1, zListBoxValue, "(", vbTextCompare) + 1
   lEnd = InStr(1, zListBoxValue, ")", vbTextCompare)
   
   iWeekValue = Val(Mid(zListBoxValue, lStart, lEnd - lStart))
   
   MsgBox "iWeekValue = " & Format(iWeekValue)
End Sub

Hope it helps.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for the help, I would like to know, since I am dealing also with decimals, like 8.25, what variable should I use? I keep losing the .25 when it converts the number over, should I avoid single and double?

Reply | Quote

Darryl,

Sorry, I assumed by your example that you were using week numbers which would normally be integers.

The range for a Single is -3.402823 E38 to 01.401298 E-45 for negative numbers and 1.4012983-45 to 3.402823 E38 for positive numbers. Unless you are using very very large numbers this should be fine.

Check out the 3rd post in this thread for some useful information and possible warning, although if you aren’t going past the 6 significant digits, i.e. 8.24 would be 3 significant digits, you should be ok with single.

Another consideration is if you only have a few variables using double will not significantly affect the performance of your application, however where you have large arrays and many variables use of double vs single can have a large impact.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote