Convert string to VBA Global Variable

Question

I'm using VBA in PowerPoint and have over 50 shapes on a slide, all of which are named. To update the color of the shape, I reference a slide later in the presentation. The slide numbers are stored as global variables and assigned in a sub (slide nums stay constant):

Sub getSlideNum()
    example1_SlideNum = 25
    example2_SlideNum = 26
    example3_SlideNum = 27
    etc.
End Sub

Based off the shapes name, I'm able to use "example1", "example2", "example3", etc. I then concatenate it with "_SlideNum" giving me the variable name.

Instead of using If Statements or a Select Case to assign each variable their slide, I want to have a short function like so:

Function getSlide(shpName As String)

     Call getSlideNum    'get the slide num for each from the previously mentioned sub

     Dim p As Variant 
     p = shpName & "_SlideNum"

     getSlide = p

End Function

As expected, getSlide returns the string example1_SlideNum and NOT 25.

Is there a way to convert the string into the variable so it uses the stored value rather than the string text?

It would be nice to have a short function like above instead of typing up 50 or so Select Case statements.

Any time you think "Variable variable names" think instead Arrays, Collections, or Dictionaries. In this case a Scripting.Dictionary would be a good choice. — JNevill
– JNevill, Commented Jan 21, 2020 at 18:50

JNevill · Accepted Answer · 2020-01-21 18:55:20Z

1

As mentioned in my comment, a dictionary would be a good choice to store these globals.

First go to Tools>>References and choose the Microsoft Scripting Runtime

Using the dictionary object:

Private SlideDict As Dictionary

Sub getSlideNum()
    Set SlideDict = New Scripting.Dictionary
    SlideDict.Add "example1_SlideNum", 25
    SlideDict.Add "example2_SlideNum", 26
    SlideDict.Add "example3_SlideNum", 27

End Sub

Sub UsingTheDictionaryExample()
    getSlideNum
    Debug.Print SlideDict("example1_SlideNum")

    'You can concatenate a variable into your dictionary key as well
    Dim mySlideNum As Integer
    mySlideNum = 2
    Debug.Print SlideDict("example" & mySlideNum & "_SlideNum")

End Sub

Running that last subroutine will print out "25" and "26" in your immediate window pane.

answered Jan 21, 2020 at 18:55

JNevill

50.6k4 gold badges46 silver badges71 bronze badges

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

1 Comment

WhenElephantsFly Over a year ago

Thanks for the crash course on Dictionaries! Works exactly as I need it to!

Collectives™ on Stack Overflow

Convert string to VBA Global Variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related