如何容易比较递增常量的值

Question

有几个常量TAX1、TAX2、TAX3，。。。，TAX_y数据、价格等的进一步数组(arrSorted(，。。我需要将值arrSorted(allRow，8(与TAX进行比较，并求一些和。但是如何增加常量TAX的结束数

for i = LBound... to UBound...
for y = 1 to 5
if arrSorted(i,8) = TAX & y then  'i dont know how TAX & y...
' SUMS HERE
end if
next y
next i

我现在有了这个反复出现的代码(这不是很好(：

Function prepareData(arrSorted() As Variant)
Dim qi As Integer
Dim qy As Integer
Dim sumPrice(0 To 4, 0 To 5) As Variant
For qi = LBound(arrSorted(), 1) To UBound(arrSorted(), 1)
Select Case arrSorted(qi, 8)
Case Is = TAX1
For qy = LBound(sumPrice, 2) To UBound(sumPrice, 2)
sumPrice(0, qy) = sumPrice(0, qy) + arrSorted(qi, qy + 4)
Next qy
Case Is = TAX2
For qy = LBound(sumPrice, 2) To UBound(sumPrice, 2)
sumPrice(1, qy) = sumPrice(1, qy) + arrSorted(qi, qy + 4)
Next qy
Case Is = TAX3
For qy = LBound(sumPrice, 2) To UBound(sumPrice, 2)
sumPrice(2, qy) = sumPrice(2, qy) + arrSorted(qi, qy + 4)
Next qy
Case Is = TAX4
For qy = LBound(sumPrice, 2) To UBound(sumPrice, 2)
sumPrice(3, qy) = sumPrice(3, qy) + arrSorted(qi, qy + 4)
Next qy
Case Is = TAX5
For qy = LBound(sumPrice, 2) To UBound(sumPrice, 2)
sumPrice(4, qy) = sumPrice(4, qy) + arrSorted(qi, qy + 4)
Next qy
Case Else
MsgBox "Alert!", vbCritical
End Select

Next qi

End Function

Answer 1

在代码执行期间，不能动态调整代码模块内的变量名。

但你可以做的是把所有的常量放进一个数组中，然后在常量数组中循环，直到找到你要找的那个。

或者，您可以将所有常量放入字典中，并将其变量名作为关键字。所以MyDictionary("TAX1") = TAX1是真的。在您的代码中，您可以执行If arrSorted(i,8) = MyDictionary("TAX" & y)。

以下是如何创建字典对象的示例：

Dim MyDictionary As Object
Set MyDictionary = CreateObject("Scripting.Dictionary")

'Putting the constants inside with their variable name as the key
MyDictionary.Add Key:="TAX1", Item:=TAX1
MyDictionary.Add Key:="TAX2", Item:=TAX2
MyDictionary.Add Key:="TAX3", Item:=TAX3
MyDictionary.Add Key:="TAX4", Item:=TAX4
MyDictionary.Add Key:="TAX5", Item:=TAX5
MyDictionary.Add Key:="TAX6", Item:=TAX6
MyDictionary.Add Key:="TAX7", Item:=TAX7
MyDictionary.Add Key:="TAX8", Item:=TAX8

'How to retrieve their values
MsgBox MyDictionary("TAX8")

Answer 2

使用字典和exists方法代替Select Case。

Function prepareData(arrSorted() As Variant)
Const TAX1 = 5
Const TAX2 = 10
Const TAX3 = 15
Const TAX4 = 20
Const TAX5 = 25
Dim qi As Integer, qy As Integer, key As String, i As Integer
Dim sumPrice(0 To 4, 0 To 5) As Variant
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add CStr(TAX1), 0
dict.Add CStr(TAX2), 1
dict.Add CStr(TAX3), 2
dict.Add CStr(TAX4), 3
dict.Add CStr(TAX5), 4
For qi = LBound(arrSorted(), 1) To UBound(arrSorted(), 1)
key = Trim(arrSorted(qi, 8))
If dict.exists(key) Then
i = dict(key)
For qy = LBound(sumPrice, 2) To UBound(sumPrice, 2)
sumPrice(i, qy) = sumPrice(i, qy) + arrSorted(qi, qy + 4)
Next qy
Else
MsgBox "Alert! Row " & qi, vbCritical, "Value=" & key
End If
Next qi
' result
Sheet2.Range("A1:F5").Value2 = sumPrice
End Function