Passing a private variable to another module

It needs to be declared as public on a General Module.

HTH

Jason

I declared "j" as a public variable but not sure if my declare statement is correct or if I have declared it in the correct part of the code. I have included all the code and were each is stored and highlighted were I am using my variable. Thanks.

This part of the code is stored under "Microsoft Excel Objects" (This is were I get a compile error).

Option Explicit

Private pWinHttpRequest As WinHttp.WinHttpRequest

Friend Function GetHistoricalData(Symbol As String, _
    Optional FromDate As Date = #12:00:00 AM#, _
    Optional ToDate As Date = #12:00:00 AM#, _
    Optional Interval As String = "Daily") As ADODB.Recordset
    
    Dim URL As String, ResponseText As String
    Dim pRecordSet As ADODB.Recordset
    Dim DateString As String, IntervalString As String
    Dim RTS() As String, RTFI
    Dim x As Long

    If FromDate <> #12:00:00 AM# Or ToDate <> #12:00:00 AM# Then
        If FromDate = 0 And ToDate > 0 Then
            FromDate = #1/1/1900#
        ElseIf FromDate > 0 And ToDate = 0 Then
            ToDate = Date
        End If
        DateString = "&a=" & Format(Month(FromDate) - 1, "00") & "&b=" _
                        & Format(FromDate, "DD") & "&c=" & Format(FromDate, "YYYY") & _
                     "&d=" & Format(Month(ToDate) - 1, "00") & "&e=" _
                        & Format(ToDate, "DD") & "&f=" & Format(ToDate, "YYYY")
    End If

    
    URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol & DateString & IntervalString
    
    pWinHttpRequest.Open "GET", URL, False
    pWinHttpRequest.Send
     
    ResponseText = pWinHttpRequest.ResponseText
    If InStr(ResponseText, "<title>Yahoo! - 404 Not Found</title>") Then
            Err.Raise 10002, "HistoricalStockDataFromYahoo.GetHistoricalData", _
            "Line " & j & ": Invalid Search Parameters."
    End If
    
    Set pRecordSet = New ADODB.Recordset
    
    pRecordSet.Fields.Append "Date", adDBDate
    pRecordSet.Fields.Append "Close", adCurrency
    pRecordSet.Open
    
    RTS = Split(ResponseText, Chr(10))
    
    For x = LBound(RTS) + 1 To UBound(RTS)
        If RTS(x) <> "" Then
            RTFI = Split(RTS(x), ",")
            pRecordSet.AddNew Array("Date", "Close"), Array(RTFI(0), RTFI(4))
            pRecordSet.Update
        End If
    Next x

    pRecordSet.MoveFirst
    Set GetHistoricalData = pRecordSet
End Function

Private Sub Class_Initialize()
    On Error Resume Next
    Set pWinHttpRequest = New WinHttpRequest
    If pWinHttpRequest Is Nothing Then
        Err.Raise 10000, "HistoricalStockDataFromYahoo.Class_Initialize", _
        "Could not create WinHttp.WinHttpRequest object..."
    End If
End Sub

This part of the code is stored under "Class Module"

Option Explicit
Public j As Integer


Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False
    
    Dim HSDFY As HistoricalStockDataFromYahoo
    Dim rs As ADODB.Recordset
    Dim i As Long
    Dim lastrow As Integer

    On Error GoTo Err_CommandButton1_Click
    
    Range("C2:D" & Rows.Count).ClearContents

    i = 2

    lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For j = 2 To lastrow
        Set HSDFY = New HistoricalStockDataFromYahoo
        Set rs = HSDFY.GetHistoricalData(Cells(j, 1).Value, Cells(j, 2).Value, Cells(j, 2).Value)
        rs.MoveFirst
        Cells(j, 3).CopyFromRecordset rs
        i = i + 1
    Next j

Exit Sub

Err_CommandButton1_Click:
    Select Case Err.Number
        Case 10000
            MsgBox Err.Description
            j = j + 1
            i = i + 1
        Case 10001
            'invalid interval
            MsgBox Err.Description
            j = j + 1
            i = i + 1
        Case 10002
            'query failed
            MsgBox Err.Description
            j = j + 1
            i = i + 1
       Case Else
            MsgBox "Invalid Search Parameters or other error.  No data was returned."
            j = j + 1
            i = i + 1
    End Select
    Resume

    Application.ScreenUpdating = True
    
End Sub

I don't understand. Put them in a Module.

You may need to delcare the Public in both the Module it is used in and the other.

Andy -

Sorry, you lost me in your comments. I'm not sure what the "get/let" properties are nor do I know where to assign the value. The whole assigning variables and letting other procedures use these variables is somewhat new to me. Realistically, I would really like to declare "j" as a private variable and then pass the value to the function that needs it. I have attached a copy of the spreadsheet so you can see where each code is stored. HTH. Thanks again for your comments.

As I said, if you will copy the Public to the top of both the sheet and the Module, it will work. Or, Dim j in the module where it was not defined as Public.

While you have few rows and J as integer will work, it is generally a good practice to dim variables that reference row numbers as Long.