时间戳

首页 > 解决方案 > 递增 const 的比较值有多容易？

arrays - 递增 const 的比较值有多容易？

问题描述

有几个常数 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

标签： arraysexcelvbacompare

解决方案

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

但是您可以做的是将所有常量放入一个数组并循环遍历常量数组，直到找到您要查找的那个。

或者您可以将所有常量放入字典中，并以它们的变量名作为键。也是如此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")

推荐阅读