将函数输出分配给变量Excel VBA时出错

Question

我正在使用一个从Microsoft复制的函数，该函数获取列号并重新分配列字母。我需要这样做来创建一个公式。我已经研究了一整天，但无法确定错误的原因(我也试图将其作为子过程来完成)。函数在它自己的模块中。我测试了它，它工作得很好:

Function ConvertToLetter(ByRef iCol As Integer) As String
  Dim iAlpha As Integer
  Dim iRemainder As Integer
  iAlpha = Int(iCol / 27)
  iRemainder = iCol - (iAlpha * 26)
  If iAlpha > 0 Then
     ConvertToLetter = Chr(iAlpha + 64)
  End If
  If iRemainder > 0 Then
     ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
  End If
End Function

给我错误的代码是:

For groups = 1 To i ' level 1 grouping
    For iCol = 24 To 136
       rCol = ConvertToLetter(iCol)
       Cells(Start(groups) - 1, rCol).Formula = "=COUNTA(" & rCol & Start(groups) & ":" & rCol & Finish(groups) & ")"
    Next
Next

我试着把函数代入公式本身:

  Cells(Start(groups) - 1, ConvertToLetter(iCol)).Formula = "=COUNTA(" & ConvertToLetter(iCol) & Start(groups) & ":" & ConvertToLetter(iCol) & Finish(groups) & ")"

调试器通过了第一个函数调用，但没有通过第二个&第三。我收到的错误类型是"期望变量或过程，而不是模块"。对于第二种情况，我得到了其他错误，我的头是如此模糊，我不能回忆起来。

非常感谢任何帮助。我已经没有主意了。非常感谢!

Answer 1

几乎不需要将列转换为字母。首先考虑使用FormulaR1C1

For groups = 1 To i ' level 1 grouping
    For iCol = 24 To 136
        lLast = Finish(groups) - Start(groups) + 1
        Sheet1.Cells(Start(groups) - 1, iCol).FormulaR1C1 = _
            "=COUNTA(R[1]C:R[" & lLast & "]C)"
    Next iCol
Next groups

如果你不喜欢R1C1，你可以直接使用Address

For groups = 1 To i ' level 1 grouping
    For iCol = 24 To 136
        Set rStart = Sheet1.Cells(Start(groups), iCol)
        Set rEnd = Sheet1.Cells(Finish(groups), iCol)
        rStart.Offset(-1, 0).Formula = _
            "=COUNTA(" & rStart.Address & ":" & rEnd.Address & ")"
    Next iCol
Next groups

Answer 2

您正在使用的功能出现故障。检查这个示例

Sub Sample()
    Dim iCol As Integer
    For iCol = 131 To 134
        Debug.Print iCol; ConvertToLetter(iCol)
    Next iCol
End Sub

Function ConvertToLetter(ByRef iCol As Integer) As String
    Dim iAlpha As Integer
    Dim iRemainder As Integer
    iAlpha = Int(iCol / 27)
    iRemainder = iCol - (iAlpha * 26)
    If iAlpha > 0 Then
       ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
       ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
End Function

131 D[
132 D
133 D]
134 D^

使用我从我提到的链接中挑选的代码。

rcol = Split(Cells(, iCol).Address, "$")(1)

不是

rCol = ConvertToLetter(iCol)

Answer 3

下面的函数将列号转换为字母:它利用了.Address方法，大大简化了生活。

Function convertToLetter(colnum)
mycell = [A1].Offset(0, colnum - 1).Address
convertToLetter = Mid(mycell, 2, Len(mycell) - 3)
End Function

但是现在我看了你的代码-当你使用Cells函数时，你应该用数字来调用它，而不是字母。所以你又有一个问题了!

尝试以下操作:

For groups = 1 To i ' level 1 grouping
    For iCol = 24 To 136
       rCol = ConvertToLetter(iCol)
       Cells(Start(groups) - 1, iCol).Formula = "=COUNTA(" & rCol & Start(groups) & ":" & rCol & Finish(groups) & ")"
    Next
Next

注意在语句的Cells(Start(groups)-1, iCol) =部分我使用了iCol而不是rCol。这可能不是唯一的错误…