![Excel - vba,将公式值分配给变量](https://linux22.com/image/1548054/Excel%20-%20vba%EF%BC%8C%E5%B0%86%E5%85%AC%E5%BC%8F%E5%80%BC%E5%88%86%E9%85%8D%E7%BB%99%E5%8F%98%E9%87%8F.png)
我正在使用 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