为什么Excel VBA编辑器会更改行连续换行? [英] Why does Excel VBA editor change line continuation word wrapping?

问题描述

我在Excel VBA中有一段很长的代码行，并且我想使用行继续符将其分成两行.但是，在我将光标从该行代码移开之后，编辑器更改了我的首选自动换行.我该如何停止这样做?

If IsEmpty([Task].Cells(rowNo, [Task[SchedDateTime]].Column)) And _
   IsEmpty([Task].Cells(rowNo, [Task[DaysAlloc]].Column)) Then

编辑器将其更改为:

If IsEmpty([Task].Cells(rowNo, [Task[SchedDateTime]].Column)) And IsEmpty( _
   [Task].Cells(rowNo, [Task[DaysAlloc]].Column)) Then

我认为很难阅读.

解决方案

停止使用带括号的标识符.此技巧适用于即时窗格中的一次性代码和快速调试器，而不适合实际的生产代码.

执行[Task]时，您将退出VBA区域，并转至宿主应用程序-Excel-该应用程序将执行Application.Evaluate("Task")，并且可能会得到一个Range对象.

从代码的外观来看，听起来Task是某些表的名称.为什么不明确说明该表的来源，而不是让Excel计算出来?

Dim taskTable As ListObject
Set taskTable = Sheet1.ListObjects("Task")

不确定要执行的操作是什么，但是您可以利用功能强大的ListObject API，而不是使用后期绑定的隐式代码，其中一半由Excel解决，一半由VBA解决.

Dim scheduleTimestampColumn As Long
scheduleTimestampColumn = taskTable.ListColumns("SchedDateTime").Index

Dim daysAllocColumn As Long
daysAllocColumn = taskTable.ListColumns("DaysAlloc").Index

Dim currentRow As ListRow
For Each currentRow In taskTable.ListRows
    If IsEmpty(currentRow.Range.Cells(ColumnIndex:=scheduleTimestampColumn).Value) And _
       IsEmpty(currentRow.Range.Cells(ColumnIndex:=daysAllocColumn).Value) _
    Then
       '...do stuff...
    End If
Next

VBA支持用括号括起来的表达式作为Application.Evaluate(或Worksheet.Evaluate，取决于上下文……的速记)的缩写，因此，它实际上比我在该答案顶部写的还要隐含的.在某些情况下，它还支持带括号的标识符，用于其中可能带有空格的名称，或者以其他方式将其用作非法的VBA标识符.

但是[TableName[ColumnName]]完全是Excel领域(表公式表示法)-VBA不知道这是什么意思，并且括号内的 方括号将其完全弄乱了.没有嵌套的括号，VBE不会混淆，也不会破坏您的缩进.但是，如果没有括号括起来的表达式，您将获得一个不混淆的VBE 和显式代码，其中说明了它的作用，并按其说的做- plus 编译时验证所有内容！ /p>

I have a line of code in Excel VBA that is long, and I want to use a line continuation to break it into two lines. However, after I move the cursor away from that line of code, the editor changes my preferred word wrapping. How do I make it stop doing that?

If IsEmpty([Task].Cells(rowNo, [Task[SchedDateTime]].Column)) And _
   IsEmpty([Task].Cells(rowNo, [Task[DaysAlloc]].Column)) Then

gets changed by the editor to:

If IsEmpty([Task].Cells(rowNo, [Task[SchedDateTime]].Column)) And IsEmpty( _
   [Task].Cells(rowNo, [Task[DaysAlloc]].Column)) Then

which I think is harder to read.

解决方案

Stop using bracketed identifiers. This trick is good for throw-away code and quick debugger stuff in the immediate pane, not for actual production code.

When you do [Task], you're stepping out of VBA land and defer to the host application - Excel - which will do Application.Evaluate("Task") and, presumably, get a Range object back.

From the looks of your code, it sounds like Task is the name of some table. Why not be explicit about where that table is coming from, instead of making Excel work it out?

Dim taskTable As ListObject
Set taskTable = Sheet1.ListObjects("Task")

Not sure what you're trying to do exactly, but you could then leverage the powerful ListObject API instead of working with late-bound implicit code half resolved by Excel and half resolved by VBA.

Dim scheduleTimestampColumn As Long
scheduleTimestampColumn = taskTable.ListColumns("SchedDateTime").Index

Dim daysAllocColumn As Long
daysAllocColumn = taskTable.ListColumns("DaysAlloc").Index

Dim currentRow As ListRow
For Each currentRow In taskTable.ListRows
    If IsEmpty(currentRow.Range.Cells(ColumnIndex:=scheduleTimestampColumn).Value) And _
       IsEmpty(currentRow.Range.Cells(ColumnIndex:=daysAllocColumn).Value) _
    Then
       '...do stuff...
    End If
Next

VBA supports bracketed expressions as a shorthand surrogate for Application.Evaluate (or Worksheet.Evaluate, depending on context... so, it's actually even more implicit than I wrote near the top of this answer). It also supports bracketed identifiers, in certain contexts, for names that can have whitespace in them, or otherwise make illegal VBA identifiers.

But [TableName[ColumnName]] is completely Excel-land (table formula notation) - VBA has no idea what that means, and the square brackets inside the bracketed expression are thoroughly confusing it. Without the nested brackets, the VBE doesn't get confused and doesn't wreck your indent. But without bracketed expressions, you get a not-confused VBE and explicit code that says what it does, and does what it says - plus compile-time validation for everything!

这篇关于为什么Excel VBA编辑器会更改行连续换行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！