How to remove commas from Val function in VBA

[TtheSkyscraper]

I want to return a value that contains commas as a separator. How do I do so?

Function GetSP500(ByVal tRange As Range) As Double


    Dim xHttp As Object
    Dim t, ticker  As String
    
    Set xHttp = CreateObject("Microsoft.XMLHTTP")
    
    ticker = tRange.Text
    
    xHttp.Open "GET", "http://finance.yahoo.com/q?s=%5EGSPC", False
    xHttp.Send


    t = xHttp.responseText
    
    t = Mid(t, InStr(t, ">SNP  <"))
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
    t = Mid(t, InStr(t, ">") + 1)
  


   
       
    GetSP500 = Val(t)


End Function

Thank you. I don't understand why the Val function doesn't do this automatically.

 

[Trevor_S]

You could try changing the last line to something like:
GetSP500 = Format(Val(t), "#,##0")

 

[Norie]

Val returns a numeric value, not a formatted numeric value.