Equivalent of Excel VALUE function for VBA

You said that B1 would actually be an INDEX/MATCH, which is making it unclear.

If you have the appropriate version of Excel, you can try this (replace B1 with your INDEX/MATCH formula):

=LET(x,B1,IF(LEFT(x)="-",VALUE(RIGHT(x,LEN(x)-1))*-1,VALUE(x)))

LET assigns the result of your INDEX/MATCH to the variable x, then re-uses it throughout the formula. x is just a name and you can use any name you want. I prefer to use a single letter as it makes the whole function shorter.

If that doesn't work and you must use VBA, then you need the following:

Function TVALUE(txt As String)
  If Left(txt, 1) = "-" Then
    'MsgBox ("Negative")
    TVALUE = Application.WorksheetFunction.NumberValue(Right(txt, Len(txt) - 1), ",", ".") * -1
  Else
    TVALUE = Application.WorksheetFunction.NumberValue(txt, ",", ".")
  End If
  
End Function

WorksheetFunction.NumberValue