Tam Coton February 2016

Assorted VBA array iteration woes

Private Sub Workbook_Open()
    Dim SourceList(0) As Workbook
    Dim PathList() As String
    Dim n As Integer
    PathList = Split("\data\WeaponInfo.csv", ",")

    ThisWorkbook.Activate
    Application.ActiveWindow.Visible = False
    Application.ScreenUpdating = False

    For n = 0 To Ubound(PathList)
        Workbooks.Open Filename:=ThisWorkbook.Path & PathList(n)
        Set SourceList(n) = ActiveWorkbook
        ActiveWindow.Visible = False
    Next

    Application.ScreenUpdating = True
    Workbooks.Open Filename:=ThisWorkbook.Path & "\HeroForge Anew 3.5 v7.4.0.1.xlsm", UpdateLinks:=3
    ActiveWindow.Visible = True

    Application.DisplayAlerts = False
    For n = 0 To UBound(SourceList)
        SourceList(n).Close
    Next
    Application.DisplayAlerts = True
End Sub

The line For n = 0 to PathList.GetUpperBound(0) is throwing a "Compile Error (invalid qualifier) whenever I try to run this macro. Specifically it highlights PathList as being the problem.

Also, if I cut out the loop and just have the contents run once (replacing the PathList(n) with "\data\WeaponInfo.csv"), it throws an "Object Variable or With block variable not set" error on the SourceList(0) = ActiveWorkbook line. What am I doing wrong?

I'm aware that the loop is currently pointless; it's futureproofing as I'm going to be using this macro to open multiple data references.

EDIT: Made changes suggested by @Jeremy below, now getting the "Object variable or With block variable not set" error on the SourceList(n).Close line.

EDIT2: Fixed the loop, again on the advice of @Jeremy, by changing Dim SourceList(1) As Workbook to Dim SourceList(0) As Workbook

Answers


Jeremy February 2016

A couple of issues:

  1. In VBA, the GetUpperBound method does not exist, it is for .NET only. Change it to Ubound function.

  2. You may run into a problem with Sourcelist(0) = ActiveWorkbook. Use the Set keyword when assigning object references.

  3. Source is not defined in your loop. ALWAYS put Option Explicit at the top of your code module to force you to declare your variables. It will save pain in the future.

  4. What are you trying to do with splitting that string? you will just get one value, which is the string you are passing in.

    Private Sub Workbook_Open()  
    
        Dim SourceList(1) As Workbook
        Dim PathList() As String
        Dim n as Integer
        PathList = Split("\data\WeaponInfo.csv", ",")
    
        ThisWorkbook.Activate
        Application.ActiveWindow.Visible = False
        Application.ScreenUpdating = False
    
        For n = 0 To Ubound(PathList)
            Workbooks.Open Filename:=ThisWorkbook.Path & PathList(n)
            Set SourceList(0) = ActiveWorkbook
        Next
    
        ActiveWindow.Visible = False
    
        Application.ScreenUpdating = True
        Workbooks.Open Filename:=ThisWorkbook.Path & "\HeroForge Anew 3.5 v7.4.0.1.xlsm", UpdateLinks:=3
        ActiveWindow.Visible = True
    
        For Each Source In SourceList
            Source.Close
        Next
    End Sub
    

Post Status

Asked in February 2016
Viewed 1,252 times
Voted 8
Answered 1 times

Search




Leave an answer