Store Array Contents 1

[osx99]

I have some vba code which I only ever need to run once when an excel workbook opens to extract URL's into an array. I have placed it in the 'ThisWorkbook' object using the event Private Sub Workbook_Open()

The format of the array is
Array(i,0) URL
Array(i,1) Weekday
Array(i,2) Venue
Array(i,3) Time

Is there a way I can store this array in VBA so that the array is accessible by other code run later in a module or Sheet object?

I want to avoid writing the contents to excel if possible and avoid re-running code each time

Thank you,
Os

 

[HarleyQuinn]

Declare the array as Public in a module, then it will be accessible from other modules, sheets etc.

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[osx99]

Almost there, just a little more guidance

In 'ThisWorkbook' i have

Private Sub Workbook_Open()
Extract_URLs
End Sub

In 'Module2' I have

Option Explicit
Public URLarray() As Variant

Function ExecuteWebRequest(url As String) As String

    Dim oXHTTP As Object
    
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "GET", url, False
    oXHTTP.send
    ExecuteWebRequest = oXHTTP.responseText
    Set oXHTTP = Nothing

End Function


Sub Extract_URLs()

    Dim regex As Object, regexMatches As Object
     
    Set regex = CreateObject("vbscript.regexp")
    regex.MultiLine = True
    regex.Global = True

    Dim URLarray(), resultArray, htmlOutput, weekdayMatches, venueMatches, URLMatches As Variant
    Dim trimmedvenueMatches As Variant
    Dim raceURL As String, URLcount As Integer, URLresultcount As Integer
    

    raceURL = "[URL unfurl="true"]http://www.xxxxxxxxxxxxxxxxxxxxxxxxx/"[/URL]
    
    htmlOutput = ExecuteWebRequest(raceURL)
    resultArray = Split(htmlOutput, "racecard_link" & Chr(34) & " href=" & Chr(34))
    URLresultcount = UBound(resultArray, 1)
    ReDim URLarray(1 To URLresultcount, 3)
    

    For URLcount = 1 To UBound(resultArray)
    
        'url in array(URLcount,0)
        regex.Pattern = "[\s\S]+?.html" 'regex for the url"
        Set URLMatches = regex.Execute(resultArray(URLcount))
        URLarray(URLcount, 0) = Trim(URLMatches(0))
        
        'weekday in array(URLcount,1)
        regex.Pattern = "[\s\S]+?/" 'regex for the url"
        Set weekdayMatches = regex.Execute(URLarray(URLcount, 0))
        URLarray(URLcount, 1) = Trim(Replace(weekdayMatches(4), "/", vbNullString))
        
        'venue in array(URLcount,2)
        regex.Pattern = URLarray(URLcount, 1) & "/[\s\S]+?[0-9]" 'regex for the url"
        Set venueMatches = regex.Execute(URLarray(URLcount, 0))
        trimmedvenueMatches = Trim(Replace(venueMatches(0), URLarray(URLcount, 1) & "/", vbNullString))
        URLarray(URLcount, 2) = Left(trimmedvenueMatches, InStrRev(trimmedvenueMatches, "_") - 1)
    
        'time in array(URLcount,3)
         URLarray(URLcount, 3) = Left(Right(URLarray(URLcount, 0), 9), 4)
    
    
    Next URLcount
       

End Sub

In 'Sheet1" this fails with run time error 9

Sub test5()
MsgBox (URLarray(1, 1))
End Sub

I've xxxxx out the url but the array does load properly when run seperately

Thanks,
Os

 

[osx99]

Stupid!! I always find the problem right after posting

I forgot to remove the original Dim URLarray()

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post