Object Required despite already defining object (EXCEL, VBA)

Question

OBJECTIVE

Pull in exchange rate data for various currencies.

APPROACH

Select Active Sheet and copy currencies-to-be-converted into an array (e.g ["EUR", "GBP", "USD"]
Open Browser and visit currency conversion website
Loop through different currencies and extract currency conversion factors
Append conversion factors to an array
Repopulate excel with newest conversion factors

HTML

<span class="amount" id="converterToAmount" style="">26.21</span>

CODE

Sub retreiveCurrencies()

Dim ws As Worksheet
Dim locals() As Variant
Dim rates As Object
Dim exchangeArray() As Variant
Dim i As Long
Dim IE As Object



'Select currencies to convert
Sheets("APPENDIX - CURRENCY CONVERTER").Activate
locals = ActiveSheet.Range("B2:B15").Value
'This should return locals = ["EUR", "GBP, "USD"]

'Prep Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'Loop through currencies and retreive rates. Paste rates into exchangeArray
For i = LBound(locals, 1) To UBound(locals, 1)
    IE.Navigate "http://www.usforex.com/currency-converter/" & locals(i, 1) & "/usd/1.00/false"
        Do While IE.Busy And Not IE.readyState = READYSTATE_COMPLETE
            DoEvents
        Loop
    '!!!!error on following line = "Object required"!!!!
    Set rates = IE.Document.GetElementById("converterToAmount").innerText
    ReDim Preserve exchangeArray(rates)

Next i

'Paste exchange rate array into currency conversion column
ActiveSheet.Range("E2:E15") = exchangeArray()



End Sub

QUESTIONS/ISSUE(S)

Currently getting error "Object Required" @ Set rates = IE.Document.GetElementById("converterToAmount").innerText despite defining Dim rates As Object. Any solution?
Is ActiveSheet.Range("E2:E15") = exchangeArray() sufficient to paste cells back into excel?

rates shouldn't be an object. You're setting it to the value of the IE element's innertext, which is a string. Try defining it as String and drop the Set keyword. — Dave
I could have sworn I had done this previously. Thank you for solving that issue. However, when I call 'MsgBox exchangeArray(i)' after the 'ReDim", I am not seeing any values. Am I misinterpreting something? For some reason, the rates are not being pulled and/or saved into the exchangeArray — jonplaca
I tend to define the target range with the extents of the variant array. e.g. .Range("E2").Resize(UBound(exchangeArray, 1), UBound(exchangeArray, 2)) = exchangeArray — user4039065
If you are planning to put the values from exchangeArray back into the worksheet, it should be a two-dimensioned array, even if one of those dimensions is only 1 to 1. — user4039065
@Comintern - I completely agree. It is just so much easier to work with apples and apples instead of oranges and apples. — user4039065

Comintern Comintern · Accepted Answer · 2016-09-26T19:22:35

The title question was already addressed by @Dave in the comments - .innerText is a String, not an Object.

That said, your array syntax is a little off - Redim Preserve actually only resizes the array - it doesn't write a value to it. You're also trying to use rates as the index instead of adding it. Also, I'd take the suggestion @Jeeped makes in the comments and also apply it to your exchangeArray. The size is fixed, and it will always be the same size as locals. That means you can just do this:

ReDim exchangeArray(LBound(locals, 1) To UBound(locals, 1), LBound(locals, 2) To UBound(locals, 2))

Once it's already set to the correct size, you don't even have to ReDim it in the loop. Just mirror the position of your "key" array:

Dim rates As String
'...

'Loop through currencies and retreive rates. Paste rates into exchangeArray
ReDim exchangeArray(LBound(locals, 1) To UBound(locals, 1), LBound(locals, 2) To UBound(locals, 2))
For i = LBound(locals, 1) To UBound(locals, 1)
    ie.navigate "http://www.usforex.com/currency-converter/" & locals(i, 1) & "/usd/1.00/false"
        Do While ie.Busy And Not ie.readyState = READYSTATE_COMPLETE
            DoEvents
        Loop
    '!!!!error on following line = "Object required"!!!!
    rates = ie.document.getElementById("converterToAmount").innerText
    exchangeArray(i, 1) = rates
Next i

Object Required despite already defining object (EXCEL, VBA)

1 Answers