I was given a VBA function that is working but I do not understand how it works and would appreciate your help because in the way it was written, the calculus is really long (basic arithmetic * 15 000 rows)
My problem comes from the definition of the Double variable Qty_Level which is defined as follow:
Dim Qty_Level(30) As Double
I have never seen a variable defined like this, with a couple of parenthesis like a function. Here's my code:
Sub cumul()
Dim i As Long
Dim j As Integer
Dim Qty_level(30) As Double
Dim Col_Niveau As Integer
Dim Col_Quantite As Integer
Dim Col_Resultat As Integer
i = InputBox("Veuillez indiquer le numéro de la première à analyser (numéro de ligne Excel)", "Ligne de départ")
Col_Niveau = InputBox("Veuillez indiquer le numéro de la colonne contenant les niveaux", "Niveaux")
Col_Quantite = InputBox("Veuillez indiquer le numéro de la colonne contenant les quantités", "Quantités")
Col_Resultat = InputBox("Veuillez indiquer le numéro de la colonne contenant les résultats", "Résultats")
Do While IsEmpty(Cells(i, Col_Niveau)) = False
If IsNumeric(Cells(i, Col_Quantite)) = True Then
Qty_level(Cells(i, Col_Niveau).Value) = Cells(i, Col_Quantite).Value
Cells(i, Col_Resultat).Value = 1
For j = 1 To Cells(i, Col_Niveau).Value
Cells(i, Col_Resultat).Value = Cells(i, Col_Resultat).Value * Qty_level(j)
Next j
End If
i = i + 1
Loop
End Sub
I do not understand how this works, and particularly how the For loop works with the Double(j)
With Dim Qty_Level(30) As Double you're declaring a static array called Qty_Level containing 31 elements of type Double (double-precision floating-point), with lower bound 0 and upper bound 30. (Unless you wrote Option Base 1 at the top of your module, in which case your lower bound is 1 and there are 30 elements total, but I doubt it.)
I see that in your current loop, you're starting your iteration at j = 1 which means you never actually access element 0. So it's better practice to explicitly specify your lower bound:
Dim QtyLevel(1 To 30) As Double
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments