excel - Excel VBA：基于列的动态变化函数

Question

Heylo，我正在尝试编写一个 excel 函数，该函数采用用户选择的范围并根据要填充的单元格排列的列执行不同的计算。下面的屏幕截图显示了列的设置。

我想将 AA5 设置为“=myFunction($AA1:$AD4)”，然后我想单击并拖动以使用自动填充功能来填充 AB5、AC5 和 AD5 具有相同的“=myFunction($AA1 :$AD4)" 但是这个 myFunction 会根据自动填充期间填充的单元格做不同的事情。

我知道如何在用户选择第一个打开的单元格 AA5 的子程序中执行此操作，并提示输入用于计算的范围。我会按照以下方式做一些事情：

Sub CalcCells()
   Dim myRange As Range
   Set myRange = Application.InputBox("Select the cells you want to use...", Type:=8)

   Dim numColumn As Long
   For numColumn = 0 To myRange.Columns.Count - 1
      Select Case numColumn
         Case Is = 0
            ActiveCell.Offset(0, numColumn).Formula = "=SUM(" + myRange.Columns(1) + ")"
         Case Is = 1
            ActiveCell.Offset(0, numColumn).Formula = "=SUMPRODUCT(" + myRange.Columns(1) + "," + myRange.Columns(2) + ")"
         Case Is = 2
            ActiveCell.Offset(0, numColumn).Formula = "=SUMPRODUCT(" + myRange.Columns(1) + "," + myRange.Columns(3) + ")/SUM(" + myRange.Columns(1) + ")"
         Case Is = 3
            ActievCell.Offset(0, numColumn).Formula = "=SUMSQ(" + myRange.Columns(4) + ")"
      End Select
   Next numColumn
End Sub

所以基本上我想做到这一点，但我希望它是一个函数，当我单击并拖动并自动填充 AB5:AD5 时，它知道单元格与哪一列对齐并基于此执行计算，并将其用作参数/参数差不多。它也不总是 4 行，因此它需要能够容纳不同的行数，但只要用户只选择相同的数据类型，.Columns 就应该可以使用它。

这可能吗？我该怎么做？感谢您提前提供任何帮助。我做了很多搜索，我不知道我是否没有找到正确的方法，但我找不到任何真正有帮助的东西。

标签： excelvbafunctiondynamicsubroutine

Answer 1

这样的事情呢？基本上，您将获得输入公式的单元格的列Application.Caller.Column。然后inputRange.Column为您提供输入范围的最左侧列。根据两者的不同，您就知道要使用哪个工作表功能。如果差值为 0，则您的公式将输入到第一列，因此您使用Sum. 如果差值为 1，则使用Sumproduct，依此类推。

Function SummarizeCells(inputRange As Range) As Double

    Dim col As Long
    col = Application.Caller.Column - inputRange.Column

    Select Case col
        Case 0
            SummarizeCells = WorksheetFunction.Sum(inputRange.Columns(1))
        Case 1
            SummarizeCells = WorksheetFunction.SumProduct(inputRange.Columns(1), inputRange.Columns(2))
        Case 2
            SummarizeCells = WorksheetFunction.SumProduct(inputRange.Columns(1), inputRange.Columns(3)) / WorksheetFunction.Sum(inputRange.Columns(1))
        Case 3
            SummarizeCells = WorksheetFunction.SumSq(inputRange.Columns(4))
    End Select

End Function

此处的示例视图：