I want to store some values in a multi dimension array.
Each row should have 3 fields: TaxHeadID, TaxHeadIDCount and TaxHeadIDAmount
I wrote a routine that is passed the TaxHeadID and TaxHeadIDAmount. The logic seemed simple but among other things I am tripping over Redim on multi dimension array.
The first thing the routine must do when it is called, is to check if the TaxHeadID is already in the array. If it is, the TaxHeadIDCount is increaded by 1 and the passed amount is added to the TAXHeadIDAmount.
I am running into an immediate problem. To check the array to determine if the TaxHEadID is already loaded I have to know the number of occorrences. When I check this using the UBound function I get a subscript out of range error.
If the TAXHeadID is not already in the array a new occurrence must be added to the array. In the new occurrence, the TAXHeadID will be loaded, the TAxHeadIDCount will be set to 1 and the TAXHeadIDAmount will be set to the passed value.
When I try to Redim the Array, either to add the create room for the first occurrence or add space for an additional occurrence I get an error, it tells me it is already dimensioned.
I’m pretty sure my logic is solid but I don’t know how to determine if I am trying to add the first item to an array, since my Ubound is giving me a subscript out of range.
I apparently don’t know how to Redim a mullti dimention array either.
I’ll include my code below. Hopefully with the description I’ve give above and the code I’m showing below someone can advise on my issues.
Option Compare Database
Option Base 1
Private Enum eTaxHeadCol
eTaxHeadID = 1
eTaxHeadCount = 2
eTaxHeadTotal = 3
Dim TaxHeadArr() As Double
Private Enum eTaxTACol
eTaxHeadID = 1
eTaxTAID = 2
eTaxTATotal = 3
eTaxTAPct = 3
Dim TaxTAArr() As Double
Public Sub addToTaxHeadArray(passedHeadID As Long, _
passedNonCostAmt As Double)
Dim lookIdx As Long
Dim newIdx As Long
Dim foundIt As Boolean
foundIt = False
' Is it already in the table
If IsNull(TaxHeadArr) Then
MsgBox ("It Is Null")
lookIdx = UBound(TaxHeadArr)
lookIdx = UBound(TaxHeadArr, 2)
For lookIdx = 1 To UBound(TaxHeadArr, 2)
If TaxHeadArr(eTaxHeadCol.eTaxHeadID, lookIdx) = passedHeadID Then
TaxHeadArr(eTaxHeadCol.eTaxHeadCount, lookIdx) = TaxHeadArr(eTaxHeadCol.eTaxHeadCount, lookIdx) + 1
TaxHeadArr(eTaxHeadCol.eTaxHeadTotal, lookIdx) = Round(TaxHeadArr(eTaxHeadCol.eTaxHeadTotal, lookIdx) + passedNonCostAmt, 2)
foundIt = True
If foundIt Then
' must add a new entry
newIdx = UBound(TaxHeadArr, 2) + 1
If newIdx = 1 Then
ReDim TaxHeadArr(eTaxHeadCol.eTaxHeadTotal, newIdx) As Double
ReDim Preserve TaxHeadArr(eTaxHeadCol.eTaxHeadTotal, newIdx) As Double
TaxHeadArr(eTaxHeadCol.eTaxHeadID, newIdx) = passedHeadID
TaxHeadArr(eTaxHeadCol.eTaxHeadCount, newIdx) = 1
TaxHeadArr(eTaxHeadCol.eTaxHeadTotal, newIdx) = passedNonCostAmt