Custom Function not working in Excel 2013 (64bit)

[Lambrix]

Good morning!

I found a cool custom function (listed below) on the web that pulls the Stock data from Yahoo Finance. It works great in excel 2010 (32bit) but when I run it on my home computer with Excel 2013 (64 bit) the function comes back as '#NAME' and I haven't been able to find solutions on the web for trouble shooting the function.

Any idea what is causing this error?

    Function StockQuote(strTicker As String, Optional dtDate As Variant)
    ' Date is optional - if omitted, use today. If value is not a date, throw error.
    If IsMissing(dtDate) Then
    dtDate = Date
    Else
    If Not (IsDate(dtDate)) Then
    StockQuote = CVErr(xlErrNum)
    End If
    End If

    Dim dtPrevDate As Date
    Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
    Dim dbClose As Double

    dtPrevDate = dtDate - 7

    ' Compile the request URL with start date and end date
    strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
    "&a=" & Month(dtPrevDate) - 1 & _
    "&b=" & Day(dtPrevDate) & _
    "&c=" & Year(dtPrevDate) & _
    "&d=" & Month(dtDate) - 1 & _
    "&e=" & Day(dtDate) & _
    "&f=" & Year(dtDate) & _
    "&g=d&ignore=.csv"

    ' Debug.Print strURL

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", strURL, False
    http.Send
    strCSV = http.responseText

    ' Debug.Print strCSV

    ' The most recent information is in row 2, just below the table headings.
    ' The price close is the 5th entry
    strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
    strColumns = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
    dbClose = strColumns(4) ' 4 means: 5th position, starting at index 0

    ' Debug.Print vbLf
    ' Debug.Print strRows(1)
    ' Debug.Print "dbClose: " & dbClose

    StockQuote = dbClose

    Set http = Nothing

    End Function

 

[iliauk]

I don't think it's related to 32/64 bit ... it uses an XML reference and if that reference were hard-coded in the code (e.g. add this dll from .... C:\Windows\System32\msxml6.dll) then the path would be dependent on 32/64 bit but it does not. So it seems that only the reference is missing.

If you go tools, references in VBA and add Microsoft XML, v6.0 does it work?

 

[RoryA]

A #NAME error would tend to indicate that either you haven't enabled macros, the code is in the wrong place, or the function call is incorrect.

 

[Lambrix]

I don't think it's related to 32/64 bit ... it uses an XML reference and if that reference were hard-coded in the code (e.g. add this dll from .... C:\Windows\System32\msxml6.dll) then the path would be dependent on 32/64 bit but it does not. So it seems that only the reference is missing.

If you go tools, references in VBA and add Microsoft XML, v6.0 does it work?

I added the reference to XML v6.0 and it still doesn't work and I double checked that Macros were enabled in the Trust Center. It's still not working. The excel file literally works on my Excel 2010 32bit version but as soon as I email the document over to my other computer the same function gives the '#NAME' error

 

[iliauk]

I can imagine - it works for me too (64 bit):

Sub StockQuoteTest()
StockQuote ("GSK")
End Sub

What if you step through the code with F8 to see what is causing the error?

 

[Lambrix]

I can imagine - it works for me too (64 bit):

Sub StockQuoteTest()
StockQuote ("GSK")
End Sub

What if you step through the code with F8 to see what is causing the error?

Are you using 2013 or 2010 64 bit?

The function does not allow you to step through the code, only with Macros.

 

[iliauk]

64 bit.

You can step through a function as you would with a sub, but only once it has been called. Try stepping through this sub:

Sub StockQuoteTest()
StockQuote ("GSK")
End Sub

 

[Lambrix]

Dang! That was it. When I ran the Sub procedure you listed it came back with "Project disabled". I had to enable the macro and then restart the excel sheet and it's working now. Thanks for the assist!