excelvba

Problem with data type- Stock (VBA, simple formulas)


I have a problem with stock data type, because i don't know how to remake it, that formulas and VBA code will be working e.g.:

I have data type: 11 bit studios S.A. (XWAR:11B) and i want to extract the ticker (in this case it is 11B), with the formula SEARCH(":", A11), and then i have the Error #ARG. In VBA it is the same situation:

Sub AAAA
If Instr(Cells(1,1), ":")>0 then
Msgbox Succes
End If
End Sub

If anyone would help me with this i will be greatfull


Solution

  • It is not clear what you are trying to do.

    On your worksheet, you would obtain the Ticker with the formula:

    =A1.[Ticker Symbol]

    In VBA, to have the Msgbox return something, you would use:

    If InStr(Cells(1, 1).Text, ":") > 0 Then
    

    But you will have to be certain that column A is not hidden or too small to show the text string.

    By the way, with regard to VBA, it is good practice to always declare your variables. In the VBA GUI, you can enforce this by Tools/Options/Editor/Code Settings and selecting Require Variable Declaration. This will place OPTION EXPLICIT at the top of each inserted module (and you can also manually add this to modules created before you selected this option). This will be extremely useful in catching typos, variables of the incorrect type, etc.