VBA Declaring Variables Assistance Please

Question

I've got a master spreadsheet that I want to populate with various data sets from team members. I've done this before and it worked well, but also conscious that at some point I will get that infamous runtime error (-2147319767 (80028029)).

In the past a colleague told me that the way to avoid it was to declare the files and sheets as variables DIM, SET, etc.

I can never quite get my head around these so could someone help me with my code please:

The code is just opening the file, unhiding a sheet, copying the data and pasting it into the main report:

    Workbooks.Open Filename:="S:\International\Interview Scoresheet (Justin).xlsm""
    Sheets("Scores").Visible = True
    Sheets("Scores").Select
    
    Windows("Interview Scoresheet (Justin).xlsm").Activate
    Sheets("Scores").Select
    Range("A2:Z" & Range("A" & Rows.Count).End(xlUp).Row).Select
    Selection.Copy
    Windows("Interview Checks.xlsm").Activate
    Sheets("Scores").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Interview Scoresheet (Justin).xlsm").Activate
    ActiveWindow.Close savechanges:=False

Workbooks.Open Filename:="S:\International\Interview Scoresheet (Soyuncu).xlsm""
    Sheets("Scores").Visible = True
    Sheets("Scores").Select
    
    Windows("Interview Scoresheet (Soyuncu).xlsm").Activate
    Sheets("Scores").Select
    Range("A2:Z" & Range("A" & Rows.Count).End(xlUp).Row).Select
    Selection.Copy
    Windows("Interview Checks.xlsm").Activate
    Sheets("Scores").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Interview Scoresheet (Soyuncu).xlsm").Activate
    ActiveWindow.Close savechanges:=False

[How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, May 25 '21 at 14:29

ceci · Answer 1 · 2021-05-25T16:35:42.140

Most people will start using vba the way they are used to work in Excel. Although it's possible, performance and stability of your code will be terrible. Therefore the first thing to learn is the use of "arrays", allowing vba to handle all your magic in memory before interacting with the sheet again.

You'll find many great tutorials about "vba arrays" but as a kickstart consider the following:

Sub Arrays()
    Dim arr
    arr = Sheet1.Range("A1").CurrentRegion.Value2 'get all data in memory
    
    Dim j As Long, i As Long 'initiate our counter vars
    Dim arr2: ReDim arr2(1 To UBound(arr), 1 To UBound(arr, 2)) '=> setup new array to modify source data
    For j = 1 To UBound(arr) 'traverse rows
        For i = 1 To UBound(arr, 2) 'traverse columns
            'here we can access each cell by referencing our array(<rowCounter>, <columnCounter>
            'e.g. arr(j,i) => if j = 1 and i = 1 we'll have the values of Cell A1
            'we can dump these values anywhere in the activesheet, other sheet, other workbook, .. but to limit the number of interactions with our sheet object we can also use our intermediant arrays
        Next i
    Next j
    'when we are ready with our data we dumb to the sheet
    With Sheet1 'the with allows us the re-use the sheet name without typing it again
        .Range(.Cells(1, 1), .Cells(UBound(arr2), UBound(arr2, 2))).Value2 = arr2 'the ubound function allows us to size the "range" to the same size as our array, once that's done we can just dumb it to the sheet
    End With
End Sub

Basically you get all data from the source sheet in memory (an array), transform it or not, and then dumb it back where you want e.g. same sheet, other sheet, other workbook, ..

Additionally, when interacting with multiple workbooks it's better to create an habit of using dedicated variable names instead of using ".activate":

Sub multiWorkbook()
    'dim vars to specific datatype
    Dim wb As Workbook, wb2 As Workbook, sh As Worksheet, arr
    Set wb = ThisWorkbook
    Set sh = wb.Sheets("sheet 1")
    
    Dim wb2 As Workbook, sh2, FilesInPath As String
    MyPath = ThisWorkbook.Path & "\SubFolder\"
    FilesInPath = "test.xlsx"
    Set wb2 = Workbooks.Open(MyPath & FilesInPath)
    Set sh2 = wb2.Sheets("sheet 1")
End Sub

Last but not least, if you want to learn: Add "option explicit" at the top of your code (above your fist sub), this will force you to declare (dim) all your vars which will give you much better insights of what's happening and avoid unexpected/unclear errors. If you don't declare a specific datatype, the default is "variant", that's why you'll notice I don't add this for the arrays in my example.

Use "F8" to go step by step trough your code and right click "Add watch" on your vars, arrays to see what's happening.

VBA Declaring Variables Assistance Please

1 Answers1