VBA Excel, loop through variables

Question

Was trying to fix below for couple of hours now and I think I'm just permanently stuck with it. To the point.

Current code:

Sub ttttt()
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer
Dim vGen As Variant
Dim vCurrent As Variant

i1 = 1
i2 = 20
i3 = 300
i4 = 4000

vGen = Array(i1, i2, i3, i4)

For Each vCurrent In vGen
    MsgBox vCurrent
Next

End Sub

Question 1 I would like to be able to return variable name, in the same way as you can return type: TypeName(). So for first loop msgbox would say: i1 and so on. I was trying different combination of .name etc but seems to fail me all the time.

Question 2 Reason for question 1 is, as a next step I would like to add something to the original value depending on the variable name.

Hopefully I would like to have something like this (obviously this code does not work, it's only for presentation):

For Each vCurrent In vGen
    vCurrent.name = vcurrent + iSomeNumber
Next

Where iSomeNumber will be coming in from another part of the program and then i could retrieve updated individual variables later on (i.e. i1 will no longer be value 1 but 1 + iSomeNumber).

Hopefully I've explained my problem plainly, if you would require any additional info please let me know.

Also I will be AFK for another 4 hours, so my reply might be a little bit delay.

Thank you in advance for any help!

Tim Williams · Accepted Answer · 2014-04-22 18:05:40Z

4

You can look at the Scripting.Dictionary object: it allows you to associate values with string "keys"

Dim d, k

Set d = CreateObject("Scripting.Dictionary")

d.Add "A", 10
d.Add "B", 20
d.Add "C", 30

For Each k in d
    Debug.print k, d(k)
Next k

d("B") = d("B") + 100

answered Apr 22, 2014 at 18:05

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Comments

dasg7 · Accepted Answer · 2014-04-22 21:57:46Z

1

Good idea Tim W.

With a little modification. Try running this one:

Sub ttttt()

Set Dict = CreateObject("Scripting.Dictionary")

Dict.Add "A1", 10
Dict.Add "B1", 20
Dict.Add "B2", 30
Dict.Add "C1", 40

ADDValue1 = 50
ADDValue2 = 100
ADDValue3 = 150

For Each k In Dict

If k Like ("*A*") Then
Debug.Print Dict(k) + ADDValue1
End If

If k Like ("*B*") Then
Debug.Print Dict(k) + ADDValue2
End If

If k Like ("*C*") Then
Debug.Print Dict(k) + ADDValue3
End If

Next k

End sub

answered Apr 22, 2014 at 21:57

dasg7

468 bronze badges

Collectives™ on Stack Overflow

VBA Excel, loop through variables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related