Assorted VBA array iteration woes

Question

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

Because there's going to be multiple files listed in it eventually, I just haven't got that far yet. — Tam Coton
– Tam Coton, Commented Feb 8, 2016 at 12:53
It seems like it is VBA (as opposed to VB.Net) rather than scope per se which seems to be the problem. VBA as a programming language is actually identical to VB6 (the pre-Net version of Visual Basic). Reading up on the differences between VB6 and VB.Net could be a good way to become aware of some of the issues involved. Something like: thescarms.com/vbasic/vb6vsvbnet.aspx — John Coleman
– John Coleman, Commented Feb 8, 2016 at 12:59

SierraOscar · Accepted Answer · 2016-02-08 12:43:04Z

3

A couple of issues:

In VBA, the GetUpperBound method does not exist, it is for .NET only. Change it to Ubound function.
You may run into a problem with Sourcelist(0) = ActiveWorkbook. Use the Set keyword when assigning object references.
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.

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

edited Feb 8, 2016 at 12:43

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

answered Feb 8, 2016 at 12:33

Jeremy

4,8582 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tam Coton Over a year ago

The split is future proofing. There will be more sources being opened, that's why I'm using a loop. Apparently string arrays are tricky beasts though, and I had trouble initialising it properly. The split solution was suggested here.

Jeremy Over a year ago

@TamCoton also there is a colon after SourceList which should go away.

Tam Coton Over a year ago

Thanks, that worked! Loop at the bottom is still giving me trouble though.

Jeremy Over a year ago

@TamCoton when you declare an array in VBA with length of 1, you are really creating an array with 2 elements, 0 and 1. You only have one item in your path list, so the 0th element has a reference to a work book, but element 1 is pointed at nothing, so it errors when you try to close it.

Jeremy Over a year ago

@TamCoton and just change the title of your question to "I don't understand VBA" :)

|

Collectives™ on Stack Overflow

Assorted VBA array iteration woes

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related