☰

Excel - vba，将公式值分配给变量

tag-icon • tag-icon microsoft-excel worksheet-function vba
Excel - vba，将公式值分配给变量

我正在使用 vba 代码根据某些标准从某一列中获取总值。 随附的是我用来解释我想要实现的目标的图像。

我想这样做是因为我将把所有的值相加并放入不同的单元格中，而且因为我在公式中使用动态变量，所以我不想多次编写相同的代码。

类似于：total = total + valueOnCell[i]

不太酷的代码：

Function siteTraffic(currentMonth As Integer, currentYear As Integer) As Integer

Dim total As Integer
Dim start As Integer
Dim step As Integer

If currentMonth = 7 Then

   Sheets("Calculations").Range("d7").Formula = "=SUM(SUMIFS(Data[Sessions],Data[Month of the year],""=" & currentMonth & """,Data[Year],""=" & currentYear & """,Data[Segment],{""=Geo - International"",""=Geo - US & LATAM""}))"


    ElseIf currentMonth = 8 Then

    Sheets("Calculations").Range("d7").Formula = "=SUM(SUMIFS(Data[Sessions],Data[Month of the year],""=" & currentMonth & """,Data[Year],""=" & currentYear & """,Data[Segment],{""=Geo - International"",""=Geo - US & LATAM""}))" _
    & "+SUM(SUMIFS(Data[Sessions],Data[Month of the year],""= 7 "",Data[Year],""=" & currentYear & """,Data[Segment],{""=Geo - International"",""=Geo - US & LATAM""}))"

     ElseIf currentMonth = 9 Then


    Sheets("Calculations").Range("d7").Formula = "=SUM(SUMIFS(Data[Sessions],Data[Month of the year],""=" & currentMonth & """,Data[Year],""=" & currentYear & """,Data[Segment],{""=Geo - International"",""=Geo - US & LATAM""}))" _
    & "+SUM(SUMIFS(Data[Sessions],Data[Month of the year],""= 7 "",Data[Year],""=" & currentYear & """,Data[Segment],{""=Geo - International"",""=Geo - US & LATAM""}))" _
    & "+SUM(SUMIFS(Data[Sessions],Data[Month of the year],""= 8 "",Data[Year],""=" & currentYear & """,Data[Segment],{""=Geo - International"",""=Geo - US & LATAM""}))"

       End If
   End Select
End Function

我正在尝试更改我的代码，以便可以动态写入，但我收到溢出错误

Function siteTraffic(currentMonth As Integer, currentYear As Integer) As Integer
'currentMonth will be 01-12
'currentYear will be >=2016
Dim step As Integer 
Dim start As Integer

For start = 1 To currentMonth

    Sheets("Calculations").Range("d7").Formula = "=SUM(SUMIFS(Data[Sessions],Data[Month of the year],""=" & start & """,Data[Year],""=" & currentYear & """,Data[Segment],{""=Geo - International"",""=Geo - US & LATAM""}))"

    step = Sheets("Calculations").Range("d7").Value + step

    Next start

    End If

我的问题是如何将每个单元格计算的值存储在变量中，这样我只需要通过迭代对每个值求和，而不是一遍又一遍地复制/粘贴相同的代码？

答案1

这可能是因为您使用整数作为 var 类型。您应该使用long,double或保留更多内存variant。如果变体类型有小数，则像这样转换它variant = CDec(variant)。

Function siteTraffic(currentMonth As Integer, currentYear As Integer) As Integer
'currentMonth will be 01-12
'currentYear will be >=2016
Dim step As Double <- you need something bigger 
Dim start As Integer

For start = 1 To currentMonth

    Sheets("Calculations").Range("d7").Formula = "=SUM(SUMIFS(Data[Sessions],Data[Month of the year],""=" & start & """,Data[Year],""=" & currentYear & """,Data[Segment],{""=Geo - International"",""=Geo - US & LATAM""}))"

    step = Sheets("Calculations").Range("d7").Value + step

Next start

End If

相关内容