Looping through variables and controls on a userform

[kelly mort]

Label1.Caption = TextBox1.Value / Var1

Var1 is my variable name. And I have them from 1 to 14 .

And same apply for the label and textboxes.

Instead of writing all 14 lines I want to reduce it.

But I don't know how to handle the variables in the loop.

Can someone pull me out?

Thanks

 

[Yongle]

Learn how to use arrays to store variables

example

Private Sub UserForm_Initialize()
    Dim a As Long, var
'store in array
    ReDim var(1 To 5)
    var(1) = "cat"
    var(2) = "lion"
    var(3) = "dog"
    var(4) = "rat"
    var(5) = "cow"
'loop through array and userform controls
    For a = 1 To 5
        Me.Controls("TextBox" & a).Text = var(a)
    Next a
End Sub

This is what the above code does :

Here is a good tutorial

Excel VBA Array - The Complete Guide - Excel Macro Mastery

This post provides everything you need to know about the Excel VBA Array. Includes a quickVBA Array reference guide and 50+ VBA Array examples.

excelmacromastery.com

 

[mikerickson]

Perhaps

Dim i As Long

For i = 1 to 14
    Controls("Label" & i).Caption = Val(Controls("TextBox" & i).Text) / Var1
Next i

 

[kelly mort]

@Yongle ,

I think i get the picture now.

Thanks

 

[kelly mort]

Perhaps

Dim i As Long

For i = 1 to 14
    Controls("Label" & i).Caption = Val(Controls("TextBox" & i).Text) / Var1
Next i

The Var1 too must switch just like the controls from 1 to 14

 

[mikerickson]

Then, I would agree with Yongle's advice and suggest you learn about arrays. (All though I wouldn't use string variables as the denominator of a fraction. )

 

[kelly mort]

Then, I would agree with Yongle's advice and suggest you learn about arrays. (All though I wouldn't use string variables as the denominator of a fraction. )

That's a valid concern. About the denominator.

Originally I am using a long Integer.

So how will I convert the string back?


Like

val(Var (a))

?

 

[mikerickson]

Ideally, you won't have an array of strings, you'll create an array of Longs.