How to avoid default property gotchas in VBA?

问题:

I only use VBA occasionally, and every time I come back to it I get caught out by some variation of the following:

I have a Range object, currentCell, that I use to keep track of what cell I'm working with in the spreadsheet. When I update this to point to a different cell, I write:

currentCell = currentCell.Offset(ColumnOffset:=1)

The problem is that I've forgotten the Set keyword, so what the above line actually does is use the default property of the Range objects:

currentCell.Value = currentCell.Offset(ColumnOffset:=1).Value

So the contents of the current cell are overwritten by what's in the new cell, and my currentCell variable hasn't changed to point to a new cell, and I get filled with rage as I realize I've made the same mistake for the hundredth time.

There probably isn't a better answer than to put a post-it on my monitor saying "Have you remembered to use Set today?", but if anyone has any suggestions to help me, I'd appreciate hearing them. In particular:

• Is there any way to turn on warnings when you implicitly use default properties? I have never used them like this on purpose, I'd always call Range.Value if that's what I meant.
• Is there any good practice for marking variables as "this should only be used to read from the spreadsheet"? In most code I write, almost all my variables are for gathering data, and it would be handy to get a warning if something starts inadvertently editing cells like this.

回答1:

Is there any way to turn on warnings when you implicitly use default properties?

No.

Is there any good practice for marking variables as "this should only be used to read from the spreadsheet"?

Well, you could make your own variable naming convention, à la Making Wrong Code Look Wrong, but you'll still have to check your own code visually and the compiler won't help you do that. So I wouldn't rely on this too much.

A better option is to circumvent the need for repeatedly redifining currentCell using .Offset altogether.

Instead, read the entire range of interest to a Variant array, do your work on that array, and then slap it back onto the sheet when you're done modifying it.

Dim i As Long
Dim j As Long
Dim v As Variant
Dim r As Range

Set r = Range("A1:D5") 'or whatever

v = r.Value 'pull from sheet

For i = 1 To UBound(v, 1)
    For j = 1 To UBound(v, 2)
        'code to modify or utilise element v(i,j) goes here
    Next j
Next i

r.Value = v 'slap v back onto sheet (if you modified it)

Voilà. No use of default properties or anything that could be confused as such. As a bonus, this will speed up your code execution.

回答2:

It took me a while to understand how you are running into problem since I do not think I have ever had this problem in spite of using range objects for years. After thinking about things, I realized I do the following 100% of the time when working with cells - I use the offset or cells functions constantly - I rarely use Set to redefine a current variable.

If I have a loop I am iterating through to go through the spreadsheet, I may do something like

Dim startRng as Range
Set startRng = range("A1")

for i = 1 to 100
   startRng.offset(i,0).value = i
   startRng.offset(i,1).value = startRng.offset(i,0).value
next i

or

for i = 1 to 100
    cells(i,0).value = i
    cells(i,1).value = cells(i,0).value
next i

Either of these notations means I almost rarely have to use Set with a range object - almost always this happens once (if at all) and indicates the first cell in a range I will iterate over or reference.

It is also really clear what the offset is - since you specify a row/column - which makes it really straightforward what is happening in the code and easier to track since it references a single cell. You don't have to track down and trace backwards to the last 3 places you update a currentCell Range object.

Adopting a style of coding using these sorts of styles should eliminate nearly all these errors you are making. I am quite serious when I say I cannot remember ever having made a similar error in all my years coding VBA - I use the offset and cells functions continuously in my code (loops in these examples, but I use similar methods with all other examples in code) rather than setting ranges to new ranges. The side effect is that when you are setting a range in your code, it is almost ALWAYS immediately following a Dim statement and much more clear.

回答3:

Whatever you choose to do, you'll need the post-it note, I'm afraid. After all, setting a range object's value to the value in another cell is a perfectly valid and common thing to do. The code has no way of knowing that you want it to do anything other than what you ask it to.

You could try checking your range object's address before and after you update it to make sure it's different, but if you remember to do that, you would be better off simply using the set keyword to update the object the way you intended.

Now that this issue has enraged you to the point of posting your question, I imagine that you'll never forget it again, regardless of how much time goes by before your next visit to VBA. So maybe you won't need the post-it note after all.

回答4:

There probably isn't a better answer than to put a post-it on my monitor saying "Have you remembered to use Set today?", but if anyone has any suggestions to help me, I'd appreciate hearing them. In particular:

I would slightly change the wording on the Post It

"Are you sure you have not forgotten using Option Explicit and Error Handling?"

Otherwise trust me there is no better way! Having said that, I would like to confirm that "Using Set" is the least of your worries. What should be on the top of your main worries is "Writing Good Code" and this doesn't come overnight. It all comes by practice.

My advice to all beginners. Never assume! For example, .Value is the default property of the range so

Range("A1") = "Blah" 

is correct. But still avoid using that.

1. Always Fully qualify your variables
2. Always Use Option Explicit
3. Always Use Error handling

For example, This works.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    On Error GoTo Whoa

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1")
    rng.Value = "Blah"

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Now let's try the above code without using the Set command. Try the below code. What happens?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    On Error GoTo Whoa

    ws = ThisWorkbook.Sheets("Sheet1")
    rng = ws.Range("A1")
    rng.Value = "Blah"

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Recommended Read.

Topic: To ‘Err’ is Human

Link: http://siddharthrout.wordpress.com/2011/08/01/to-err-is-human/

回答5:

I think enderland has highlighted the basic solution, which is to handle processing multiple cells in a loop. To take it further, I'd suggest using a For Next loop for cycling through cells. Probably on of the most common bits of code I write is something like:

Dim cell as Excel.Range
Dim rngCellsToProcess as Excel.Range

Set rngCellsToProcess = 'whatever you set it to
For each cell in rngCellsToProcess 
   'do something
Next cell

This won't eliminate the need for Set, but may help remind you to use it, while making it clearer what's going on.

回答6:

Maybe write your own custom function and use it instead ?

Sub offset_rng(ByRef my_rng As Range, _
    Optional row As Integer, Optional col As Integer)
    Set my_rng = my_rng.Offset(row, col)
End Sub

Can be used like this:

Sub test()
    Dim rng As Range
    Set rng = Range("A1")
    offset_rng my_rng:=rng, col:=1
    rng.Value = "test1"
    offset_rng my_rng:=rng, col:=1
    rng.Value = "test2"
    offset_rng my_rng:=rng, col:=1
    rng.Value = "test3"
    offset_rng my_rng:=rng, col:=1
    rng.Value = "test4"
End Sub