时间戳

首页 > 解决方案 > Excel VBA：#VALUE！某些选定范围的错误

excel - Excel VBA：#VALUE！某些选定范围的错误

问题描述

Heylo，我最近一直在学习 Excel VBA 和宏，但我在进行练习时遇到了问题……我有一个开始产生 #VALUE 的函数！某些选定范围的错误，我对如何修复它有点困惑。该函数根据正在自动填充的单元格进行不同的计算。功能如下：

Function CalculateStuff(myRange As Range) As Double
   Application.Volatile

   Dim numRows As Long
   numRows = myRange.Rows.Count

   Dim whatColumn As Long
   whatColumn = Application.Caller.Column - myRange.Column

   Dim i As Long
   Dim thing As Double
   Dim mainThing As Double

   Select Case whatColumn
      Case 0
         CalculateStuff = WorksheetFunction.Sum(myRange.Columns(1))
      Case 1
         CalculateStuff = WorksheetFunction.SumProduct(myRange.Columns(1), myRange.Columns(2))
      Case 2
         CalculateStuff = WorksheetFunction.SumProduct(myRange.Columns(1), myRange.Columns(3)) / WorksheetFunction.Sum(myRange.Columns(1))
      Case 3
         CalculateStuff = WorksheetFunction.SumSq(myRange.Columns(4))
      Case 4
         For i = 1 To numRows
            thing = myRange(i, 1) * WorksheetFunction.SumSq(myRange(i, 3), myRange(i, 5))
            mainThing = mainThing + thing
         Next i
         CalculateStuff = mainThing
    End Select

End Function

然后我在子程序中使用它来填充活动单元格的公式并自动填充到活动单元格的右侧。子程序如下：

Sub RunCalculateStuff()
   Dim initialRange As Range
   Set initialRange = Application.InputBox("Please select the cells in the first column you would like to use...", Type:=8)

   Dim finalRange As Range
   Dim i As Long
   i = 0
   For Each initialCell In initialRange
      If i = 0 Then
         Set finalRange = ActiveSheet.Range(initialCell, initialCell.Offset(0, 4))
         i = i + 1
      Else
         Set finalRange = Application.Union(finalRange, ActiveSheet.Range(initialCell, initialCell.Offset(0, 4)))
         i = i + 1
      End If
   Next

   ActiveCell.Formula = "=CalculateStuff(" + finalRange.Address + ")"

   ActiveCell.AutoFill Destination:=ActiveSheet.Range(ActiveCell, ActiveCell.Offset(0, 4))
End Sub

该子例程的工作原理是让用户选择他们想要使用的第一列中的单元格，然后循环遍历这些单元格并将单元格抓取到偏移量为 (0, 4) 的位置，并将该范围添加到整个范围中。然后将该范围馈送到此功能并继续运行。

这里是#VALUE！错误出现...仅当我选择的单元格不连续时才会发生...我的意思是，如果我使用初始获取框选择范围 AA1:AA4，它就可以正常工作。如果我分别选择 AA1、AA2、AA3、AA4 范围，它工作得很好。但是，如果我选择范围 AA1、AA3，我会得到 #VALUE！错误。我觉得它与跳过行有关，但我真的不明白为什么，因为我在自己的范围内做一个联合。另外，当我尝试在第一次计算中对范围的第一列求和时，它失败了，然后在其余部分也失败了。下面的截图说明我的意思。

工作范围：

工作范围

破碎范围：

破碎范围

预先感谢您的帮助！对此，我真的非常感激。

标签： excelvbafunctionsubroutinevalueerror

解决方案

这似乎对我有用。发布以防万一您没有运气，或者如果您需要灵感。请注意，我没有进行任何错误检查来查看您输入的所有元素是否都是范围。

Function CalculateStuff2(ParamArray Rngs()) As Double

    Dim i As Integer
    Dim col As Long
    Dim tmpRng As Range
    Dim tmpDbl As Double
    Dim divisor As Double
    Dim IsCase2 As Boolean
    Dim numRows As Long, r As Long

    For i = LBound(Rngs()) To UBound(Rngs())
        Set tmpRng = Rngs(i)
        col = Application.Caller.Column - tmpRng.Column
        numRows = tmpRng.Rows.Count

        Select Case col
            Case 0
                tmpDbl = tmpDbl + WorksheetFunction.Sum(tmpRng.Columns(1))
            Case 1
                tmpDbl = tmpDbl + WorksheetFunction.SumProduct(tmpRng.Columns(1), tmpRng.Columns(2))
            Case 2
                IsCase2 = True
                tmpDbl = tmpDbl + WorksheetFunction.SumProduct(tmpRng.Columns(1), tmpRng.Columns(3))
                divisor = divisor + WorksheetFunction.Sum(tmpRng.Columns(1))
            Case 3
                tmpDbl = tmpDbl + WorksheetFunction.SumSq(tmpRng.Columns(4))
            Case 4
                For r = 1 To numRows
                    tmpDbl = tmpDbl + tmpRng(r, 1) * WorksheetFunction.SumSq(tmpRng(r, 3), tmpRng(r, 5))
                Next r
        End Select
    Next i

    If IsCase2 Then
        CalculateStuff2 = tmpDbl / divisor
    Else
        CalculateStuff2 = tmpDbl
    End If
End Function

推荐阅读