VBA - Dynamic Variable Name in a Loop

Question

I have a macro contains fixed name of a variable except that the last character changes: i.e.(BatchTotal1, BatchTotal2, BatchTotal3,...,etc.).

Each BatchTotal's value of the above is defined in another macro so I publicly defined them at first place.

The problem is that I tried to use them in a loop but in vain, BatchTotal(i) always return 0 please check my code:

Public BatchTotal(1 To 5) As Double
Sub priority_calculation()

'Some Code

For i = 1 To 5
  If BatchTotal(i) > 0 Then           
    Cells(k, 2).PasteSpecial Paste:=xlPasteValues
    Cells(k, 3) = C1
    Cells(k, 8) = Q1
  End If
Next i

End Sub

Show the code where you assign the values. You need to assign to `BatchTotal(1)` not `BatchTotal1`. Make sure you use `Option Explicit` at top of your code and declare all the variables `Dim C1 as , Q1 as `. — CDP1802, Mar 18 '23 at 17:01

score 1 · Answer 1 · answered Mar 18 '23 at 17:13

1

Ref your previous post, if BatchTotal1,2,3,4 are textboxes on Userform1 you can set the array values with

Dim i as Long
For i = 1 To 5
    BatchTotal(i) = UserForm1.Controls("BatchTotal" & i)
Next

answered Mar 18 '23 at 17:13

CDP1802

13,871
2
7
17

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ · Answer 2 · 2023-03-18T19:59:17.427

-1

Sub doTheJob()
   Dim mdl As String, v As Variant, i as Integer
   mdl = "Module2"    ' the module where the BatchTotal1, BatchTotal2 ...  implemented
   For i = 1 To 5
     v = Application.Run(mdl & ".BatchTotal" & i)
     If v > 0 Then
       Cells(k, 2).PasteSpecial Paste:=xlPasteValues
       Cells(k, 3) = C1
       Cells(k, 8) = Q1
     End If
   Next i

End Sub

edited Mar 18 '23 at 19:59

answered Mar 18 '23 at 18:24

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ

1,658
1
4
14

https://stackoverflow.com/questions/2695198/calling-a-sub-or-function-contained-in-a-module-using-callbyname-in-vb-vba – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Mar 18 '23 at 18:27

VBA - Dynamic Variable Name in a Loop

2 Answers2