CODEWITHSUNDEEP
vba
user8645201
user8645201

Reputation:

Referencing Excel Cells in VBA

My main problem is assigning a variable an manipulating it in VBA.

I know that this code works, but I'm not sure if it this is a proper way to assign a variable in VBA like I have with currentcell = Cells(i, 1).Value 

Sub C()
Dim i As Integer
For i = 1 To 4
currentcell = Cells(i, 1).Value
MsgBox currentcell
Next i
End Sub

Upvotes: 1

Views: 390

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

What the macro recorder and a lot of tutorials won't tell you, is that these calls are implicitly referring to the ActiveSheet, which is obviously implicitly in the ActiveWorkbook. When all you ever have to deal with is a single worksheet, it's probably fine, but reality is that it's never the case, and code written with implicit references to the ActiveSheet is incredibly frail, bug-prone, and the underlying cause behind way too many Stack Overflow questions tagged with :

  • Cells
  • Range
  • Name
  • Rows
  • Columns

Whenever you use any of those, you're making calls against some global-scope Property Get accessor that "conveniently" fetches the ActiveSheet reference for you:

Rubberduck toolbar showing "Excel._Global.Cells (property get accessor:Range)"

Screenshot of Rubberduck's toolbar, showing the declaration site of the selected code. Disclaimer: I manage that open-source VBIDE add-in project.

So yes, this "works", but what you'll want is to work off an explicit Worksheet object reference instead. There are many ways to get a Worksheet object reference, but the best one is to not care about where the worksheet is coming from, and take it as a parameter:

Sub DoSomething(ByVal ws As Worksheet)
    Dim currentCell As Variant
    Dim i As Integer
    For i = 1 To 4
        currentcell = ws.Cells(i, 1).Value
        'in-cell error values can't be converted to a string:
        If Not IsError(currentCell) Then MsgBox currentcell
    Next i
End Sub

This takes the responsibility of knowing exactly what worksheet to work with away from the procedure, and gives it to the calling code, which can look like this:

DoSomething Sheet1 'uses the sheet's CodeName property

Or this:

DoSomething ThisWorkbook.Worksheets("Period" & n)

Or whatever. There are plenty of ways, each with their own gotchas - often you'll see something like this:

DoSomething Sheets("Sheet1") 'where "Sheet1" is in ThisWorkbook

Except Sheets implicitly refers to the ActiveWorkbook which may or may not be ThisWorkbook, and the Sheets collection could return a Chart object, which isn't a Worksheet. Also if "Sheet1" is in ThisWorkbook (the workbook with the code that's running), then referring to a worksheet by name means your code breaks as soon as the user renames the tab for that sheet.

The most robust way to refer to a worksheet that exists at compile/design-time, is by its CodeName. Select the sheet in the Project Explorer (Ctrl+R), then bring up the Properties toolwindow (F4), and set its CodeName by changing the (Name) property to whatever you need: VBA defines a global-scope global object variable by that name, so you can call it MyAwesomeReport and then refer to it as such in code:

DoSomething MyAwesomeReport

And that code will not break if the user changes the worksheet's "name".

Your code (however small that snippet might be) has other issues, notably it's using undeclared variables, which means you're not using Option Explicit, which is another thing that will end up biting you in the rear end and come to Stack Overflow with an embarrassing question that boils down to a typo - because without Option Explicit specified at the top of every module, VBA will happily compile a typo, making your code behave strangely instead of simply not compiling. These bugs can be excruciatingly hard to find, and they're ridiculously easy to prevent:

Use. Option. Explicit. Always.

Upvotes: 1

Related Questions