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
– CDP1802, Commented Mar 18, 2023 at 17:01

CDP1802 · Accepted Answer · 2023-03-18 17:13:05Z

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, 2023 at 17:13

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ · Accepted Answer · 2023-03-18 19:59:17Z

-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, 2023 at 19:59

answered Mar 18, 2023 at 18:24

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

1,6901 gold badge6 silver badges16 bronze badges

1 Comment

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Over a year ago

stackoverflow.com/questions/2695198/…

Collectives™ on Stack Overflow

VBA - Dynamic Variable Name in a Loop

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related