如何避免VBA中的默认属性问题？ [英] How to avoid default property gotchas in VBA?

问题描述

我只是偶尔使用VBA，每当我回到它时，都会被以下一些变化所捕获：

我有一个范围对象， currentCell ，我用来跟踪电子表格中正在使用的单元格。当我更新它指向一个不同的单元格，我写：

  currentCell = currentCell.Offset（ColumnOffset：= 1）
  

问题是我忘记了 Set 关键字，所以上述行实际上是使用范围对象的默认属性：

  currentCell.Value = currentCell.Offset（ColumnOffset：= 1）.Value 
  

所以当前单元格的内容被新单元格中的内容覆盖，而我的 currentCell 变量没有改变为指向一个新的单元格，并且我被填充愤怒，因为我意识到我犯了同样的错误，第一百次。

可能没有一个更好的答案，把一个post-it在我的显示器上说你今天记得要使用Set吗？，但是如果有任何建议帮助我，我会很乐意听到他们的意见。特别是：

• 当您隐式使用默认属性时，是否有任何方式打开警告？我从来没有按照这样的目的使用过，如果这是我的意思，我总是会调用 Range.Value 。


• 是否将变量标记为这只应用于从电子表格读取的任何好的做法？在我写的大多数代码中，我几乎所有的变量都是用于收集数据的，如果有人开始无意中编辑这样的单元格，那么得到警告是很方便的。

解决方案

当您有任何方法可以打开警告隐含使用默认属性？

否。

有没有将变量标记为这只能用于从电子表格中读取的良好做法？

嗯，你可以自己创建变量命名约定，àla 使错误代码看起来错误，但你仍然必须视觉上检查你自己的代码，编译器不会帮你做到这一点。所以我不会太依赖这个。

一个更好的选择是避免需要使用 .Offset重复重新定义 currentCell / code>

相反，读取Variant数组的所有兴趣范围，对该数组进行工作，然后在完成修改后将其重新回到工作表上。

  Dim i As Long 
 Dim j As Long 
 Dim v As Variant 
 Dim r作为范围
 
设置r =范围（A1：D5）'或任何
 
v = r.Value'从表单
 
对于我= 1对于UBound（v，1）
对于j = 1对于UBound（v，2）
'修改或使用元素v（i，j）的代码在这里
下一个j 
 Next i 
 
 r.Value = v'slap v回到工作表（如果你修改了）
  

Voilà。不使用默认属性或任何可能被混淆的东西。作为奖励，这将加快您的代码执行。

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.

解决方案

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.

这篇关于如何避免VBA中的默认属性问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！