☰

VBA 正确大小写代码

tag-icon • tag-icon microsoft-excel vba
VBA 正确大小写代码

有人能解释为什么这段代码返回运行时错误'91'吗？

Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In rng
Next cell
If Not cell.HasFormula Then
End If
cell.Value = WorksheetFunction.Proper(cell.Value)

答案1

For Each cell In rng

Next cell

这将遍历选定范围的每个元素，但不执行任何操作。循环结束时，即代码遍历完每个元素后，存储在对象中的值将cell被清除。

If Not cell.HasFormula Then

End If

现在，cell是一个空变量，即它的值是Nothing。Nothing没有任何属性，具体来说，它没有HasFormula属性

我认为你想要的代码是：

For Each cell In rng
    If Not cell.HasFormula Then
        cell.Value = WorksheetFunction.Proper(cell.Value)
    End If
Next cell

我应该补充一点，我还没有检查这段代码是否真的会运行。这只是你编写的命令的“正确”执行顺序。

答案2

一开始我以为我可以通过测试代码来改进已经获得支持的答案。我需要添加更多代码才能使其可运行，下面是结果。在代码注释中对适用的地方进行了解释。

Option Explicit                     ' always use Option Explicit
                                    ' Option Explicit can be set by default for all new Modules
                                    ' Tools > Options > Require Variable Declaration > OK

Sub sbWorksheetFunctionProper()     ' A declaration of a Subroutine is required

    Dim rngMyRange As Range         ' declare each variable on a separate line to avoid ambiguity
    Dim rngCell As Range            ' use a meaningful Naming Convention as in "rngCell" rather than "cell"

    Set rngMyRange = Selection      ' Selection here refers to the ActiveWorksheet, this can be in another workbook

    For Each rngCell In rngMyRange  ' this is a For Each... Next loop
                                    ' For Each makes the loop pick Each Item in a Collection
                                    ' the Item in this case is a Cell
                                    ' the Collection in this case is a Range
                                    ' a Range in Excel is a Collection of Cells
        
        If rngCell.HasFormula Then  ' better to use a positive condition rather than a contrived negative
            
            ' do nothing if the Cell has a Formula in it
            ' do nothing if the Cell has a Formula in it
        
        Else                        ' Else donotes the start of code executed on the negative of the If condition
            
            ' in this case this is what this Subroutine does
            ' in this case this is what this Subroutine does
            
            rngCell.Value = WorksheetFunction.Proper(rngCell.Value)
                                    ' this statement calls an Excel Worksheet Function
                                    ' in this case the function is Proper() - written as =PROPER() in a Worksheet
                                    ' the text "microsoft visual basic for applications"
                                    ' would be converted to "Microsoft Visual Basic For Applications" with all words Capitalised
        
        End If                      ' End If denotes the end of code executed as a result of the If condition
    
    Next rngCell                    ' Next denotes where the For Each condition is re-evaluated to move to the next Item

End Sub                             ' End Sub denotes the end of code for this Subroutine

相关内容