Activecell.row and others?

[danjuma]

Hello,

I have an excel spreadsheet that connects to a stock brokerage datafeed. One of the data I can get into the spreadsheet is something called 'Historical data'. So to get the historical data for say stock ABC, I have to enter ABC in cell A8 in the 'Historical Data' worksheet in the spreadsheet. I have to then click on this cell to select it, and then click on a macro button labelled 'Request Historical Data'. The data will then be downloaded for stock ABC and displayed in an automatically created worksheet in the spreadsheet. The next cell - A9 automatically gets selected in readiness for if you wanted to request historical data for the next stock in the list. So if cell A9 contained stock XYZ, and you then click the macro button 'Request Historical Data', the historical data for XYZ will be downloaded and displayed in another automatically created worksheet in the spreadsheet. Then the next cell - A10 gets selected, an so it goes on. So basically to get the Historical Data for say 20 stocks, you have to enter the stocks id in cells A8 to A28, and manually click on each cell and manually click on the 'Request Historical Data' macro button one at a time.

One of the things I want to do is to automate this process. So, basically, I click on 'Request Historical Data' button, first cell A8 automatically gets selected, the data gets downloaded, and the next cell A9 automatically gets selected, and the data gets downloaded, and the next cell A10 gets selected, downloaded and so on.

I not well versed in VBA, and I am currently learning it bit by bit. I have looked at the code for the 'Request Historical Data' macro (one of the procedures amongst a host of codes in the spreadsheet). This I ahve copied below. If somebody could look at the code and amend it for what I want it to do, it would be much appreciated. If not, I am attempting to amend it myself, with help from your kind selves.

Now first stage, I am guessing the bit in the code below:
id = ActiveCell.row
is what gets the stock id (eg. ABC, XYZ etc) from the cells. From my understanding of ActiveCell.row, I thought this returns the row number, so in this case for cell A8, the row number will be 8, and id = 8.
So what I did to scroll through the cells was

Dim r As Interger
For r = 8 To 28
id = 8
Next

However, this did not work!

Please, note like I aready stated, I am just starting to learn VBA, so please bear with me. Many thanks.

' request historical data
Public Sub RequestHistoricalData_Click()
    If Not (objTWSControl Is Nothing) Then
        If objTWSControl.m_isConnected Then
 
            Dim id As Long
 
          [B][COLOR=blue]  id = ActiveCell.row[/COLOR][/B]
            ' create contract
            Set objTWSControl.m_contractInfo = objTWSControl.m_TWSControl.createContract()
 
            ' fill contract structure
            With objTWSControl.m_contractInfo
                .symbol = UCase(Cells(id, Columns(COLUMN_SYMBOL).column).value)
                .secType = UCase(Cells(id, Columns(COLUMN_SECTYPE).column).value)
                .expiry = Cells(id, Columns(COLUMN_EXPIRY).column).value
                .Strike = Cells(id, Columns(COLUMN_STRIKE).column).value
                .Right = UCase(Cells(id, Columns(COLUMN_RIGHT).column).value)
                .multiplier = UCase(Cells(id, Columns(COLUMN_MULTIPLIER).column).value)
                .exchange = UCase(Cells(id, Columns(COLUMN_EXCH).column).value)
                .primaryExchange = UCase(Cells(id, Columns(COLUMN_PRIMEXCH).column).value)
                .currency = UCase(Cells(id, Columns(COLUMN_CURRENCY).column).value)
                .localSymbol = UCase(Cells(id, Columns(COLUMN_LOCALSYMBOL).column).value)
                .includeExpired = Cells(id, Columns(COLUMN_INCLUDEEXPIRED).column).value
            End With
 
            ' combo legs
            If Cells(id, Columns(COLUMN_SECTYPE).column).value = SECTYPE_BAG And _
            Cells(id, Columns(COLUMN_COMBOLEGS).column) <> STR_EMPTY Then
                ' create combo leg list
                objTWSControl.m_contractInfo.ComboLegs = objTWSControl.m_TWSControl.createComboLegList()
 
                ' parse combo legs string
                Call Util.ParseComboLegsIntoStruct(Cells(id, Columns(COLUMN_COMBOLEGS).column).value, objTWSControl.m_contractInfo.ComboLegs)
            End If
 
            ' under comp
            If Cells(id, Columns(COLUMN_SECTYPE).column).value = SECTYPE_BAG And _
            Cells(id, Columns(COLUMN_UNDERCOMP).column) <> STR_EMPTY Then
                ' create under comp
                objTWSControl.m_contractInfo.underComp = objTWSControl.m_TWSControl.createUnderComp()
 
                ' parse under comp info
                Call Util.ParseUnderCompIntoStruct(Cells(id, Columns(COLUMN_UNDERCOMP).column).value, objTWSControl.m_contractInfo.underComp)
            End If
 
 
            ' query specification
            Dim endDateTime As String, duration As String, barSize As String, whatToShow As String
            Dim useRTH As Long
            Dim formatDate As Long
            endDateTime = STR_EMPTY
            duration = STR_EMPTY
            barSize = STR_EMPTY
            whatToShow = STR_EMPTY
            useRTH = True
            formatDate = 1
 
            If Cells(id, Columns(COLUMN_ENDDATETIME).column).value <> STR_EMPTY Then
                endDateTime = UCase(Cells(id, Columns(COLUMN_ENDDATETIME).column).value)
            Else
                endDateTime = Format(Now, "YYYYMMDD HH:mm:SS") + " GMT"
            End If
 
            duration = UCase(Cells(id, Columns(COLUMN_DURATION).column).value)
            barSize = Cells(id, Columns(COLUMN_BARSIZE).column).value
            whatToShow = UCase(Cells(id, Columns(COLUMN_WHATTOSHOW).column).value)
            useRTH = Cells(id, Columns(COLUMN_RTHONLY).column).value
            formatDate = Cells(id, Columns(COLUMN_DATEFORMAT).column).value
 
            ' call reqHistoricalDataEx method
            Call objTWSControl.m_TWSControl.reqHistoricalDataEx(id + ID_HISTDATA, objTWSControl.m_contractInfo, endDateTime, duration, barSize, whatToShow, useRTH, formatDate)
            Cells(id, Columns(COLUMN_STATUS).column).value = STR_PROCESSING
 
            ActiveCell.Offset(1, 0).Activate
        Else
            MsgBox (STR_TWS_CONTROL_NOT_CONNECTED)
        End If
    Else
        MsgBox (STR_TWS_CONTROL_NOT_INITIALIZED)
    End If
End Sub

 

[tweedle]

To make this bit work

Dim r As Interger
For r = 8 To 28
id = 8
Next

would need to be

Dim r As Integer
For r = 8 To 28
id = [COLOR=blue]r[/COLOR]
Next

and you're correct on id being the row number.
It's used later in Cells which has a parameter for the row number.

 

[danjuma]

To make this bit work

Dim r As Interger
For r = 8 To 28
id = 8
Next

would need to be

Dim r As Integer
For r = 8 To 28
id = [COLOR=blue]r[/COLOR]
Next

and you're correct on id being the row number.
It's used later in Cells which has a parameter for the row number.

Thanks for your reply. Actually my code was written as id = r, not id = 8. Typo error in my thread above. Anyway will have a look at the code again later this evening when I get home and give it another try. May be I have made a typo error somehwere.

 

[danjuma]

Ok, I have managed to sort this out myself.