Avatar of DrTribos
DrTribos
Flag for Australia asked on

Modify variables in VBA

Hi All

I have a bunch of variables in my code with the same name except for the last character.

Rather than have a select case statement I'd like to combine 2 variables...

e.g. I might have the following variables...

Dim PicJPG as string
Dim PicPNG as string
Dim PicBMP as string
Dim PicGIF as string

I want to assign a value to the variable like this

Pic & right(filename, 3) = thisdocument.path & filename

instead of

Select Case right(filename, 3)
Case JPG
   PicJPG = ....
Case....

and so on... I have a lot of cases!


This is specifically for VBA

Thanks!
Visual Basic ClassicProgrammingVB Script

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
SOLUTION
Meir Rivkin

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DrTribos

ASKER
Thank you - I am not sure how to implement this solution but will have a go...

I am not sure how this will impact on my project as some of the variables are global.
Meir Rivkin

it doesn't matter that they are global, so the dictionary variable will be global.
the problem u are facing is u try to avoid using Select Case, in order to map each file to its matched variable.
now, think of the dictionary is multiple variables structure which identified by the image type, or particularly by: right(filename, 3).
i don't know how you go through all different image files, but basically u simply assign the filename using my code.
when u want to use the filename, u get it from the dictionary by providing the filetype:
to get the bmp filename:
dict["bmp"]

to get the jpg filename:
dict["jpg"]
DrTribos

ASKER
Thank you,  I'll take a look...  might be a little while before I can test / experiment
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Meir Rivkin

ok man good luck
Bill Prew

Just as another bit of info, you can also sometimes use the EVAL() function for this.  For example this would be valid:

x = Eval("Pic" & right(filename, 3))

That would set variable x to the value of the variable named "PicXXX" where XXX is the file extension.

But unfortunately I don't think there is any way to do what you want, namely:

x = Eval("Pic" & right(filename, 3)) =  thisdocument.path & filename

I don't think that is supported.

If you do go the dictionary approach, I would recommend using one of more functions to manage the dictionary stuff, possibly even a small class for that.  That will keep the main code clean and hide some of the work behind the scenes.  Depending on your specifics you may have to handle cases of looking for an extension that you didn't get a file for, etc, and that will require some extra logic on each use.

Also be aware that once you load the elements into the dictionary, another very useful feature is the ability to do a "for each" over the dictionary and process each entry one at a time, without knowing the exact values in the dictionary.

~bp
DrTribos

ASKER
Hi Sedgwick & Bill

Sorry I have not had a chance to test this.  Hope to do it today.

Thanks for your patience!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
DrTribos

ASKER
Hi Sedgwick

Man... I just can not get your approach to work!  I get all sorts of errors.  I went to online help and modified my code, but I still get errors.

Original (user defined type not defined):
Sub modVar()
Dim dict As New Scripting.Dictionary
Dim filename As String
filename = "myfile.jpg"
dict.Add Right(filename, 3), ThisDocument.Path & filename
'msgbox dict["jpg"]
MsgBox ""
End Sub

Open in new window


Modified (object required):
Sub modVar()
Dim dict
Dim filename As String
Set dict = CreateObject(Scripting.Dictionary)
filename = "myfile.jpg"
dict.Add Right(filename, 3), ThisDocument.Path & filename
dict.Add Right(filename, 3), ThisDocument.Path
'msgbox dict["jpg"]
MsgBox ""
End Sub

Open in new window

DrTribos

ASKER
Hi Bill... um... Enum?  I can not find an Eval anywhere!
Bill Prew

Sorry, in VBA I believe it's called EVALUATE.

~bp
Your help has saved me hundreds of hours of internet surfing.
fblack61
DrTribos

ASKER
Hi Bill,

Can't find Evaluate either :-(
Bill Prew

Okay, the Eval or Evaluate concept wasn't going to give you what you needed anyway, so let's not worry about those.  I think I was confusing older versions of VBA and/or VBScript, so sorry for the confusion.

The dictionary is probably the best approach in this case, and this seems to work for me (in Excel). You will need to include a reference to "Windows Script Host Object Model" though, don't forget that.

Sub modVar()
    Dim dict As Object
    Dim filename As String
    Set dict = CreateObject("Scripting.Dictionary")
    filename = "myfile.jpg"
    dict.Add Right(filename, 3), filename
    MsgBox dict("jpg")
End Sub

Open in new window

~bp
DrTribos

ASKER
Thanks! Have not had time to test the latest, will let you know..
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
DrTribos

ASKER
Hi Bill,

I don't think this will do what I asked... I understand the code a bit better, changed it to:
Sub modVar()
    Dim dict As Object
    Dim filename As String
    Dim file As String
    Set dict = CreateObject("Scripting.Dictionary")
    filename = "MyFile.jpg"
    file = Right(filename, 3)
    dict.Add file, filename
    MsgBox dict(file)
End Sub

Open in new window


Unfortunately I think you are correct in your comment at ID: 39161059.  At least I understand the scripting dictionary a bit better now ;-)

Pls let me know if you have any other ideas... you are right I am basically trying to concatenate 2 strings to form a Dimmed variable.  I think the dictionary would work for some similar situations but not for what I was hoping to do (at least not the way I was trying to do it).  At any rate I ended up using Select Case.

Cheers,
Bill Prew

That seems to do what you wanted, what doesn't it do that you need it to?

~bp
DrTribos

ASKER
Thanks Bill,

Say I have variables called ImgA, ImgB, ImgC and so on...

The Img part is constant, in a manner of speaking.  I was hoping to name my image folders like:  folderA, folderB, folderC and so on...

I use code to get the folder name.  Instead of saying ' ImgA = "pictureXYZ" '  I was hoping to say ' Img & Right(folderA, 1) = "pictureXYZ" '

Cheers,
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Bill Prew

Okay, this still sounds like a good use for a dictionary, unless you absolutely need individual variables based on some other logic in the script that I am not seeing.

It feels like you want to associate a full file name with an image type like:

JPG => c:\foldername\filename.jpg
PNG => c:\foldername\filename.png
BMP => c:\foldername\filename.bmp
GIF => c:\foldername\filename.gif

then I assume later in the code you want to be able to get the filename again based on the three character image type?

~bp
DrTribos

ASKER
Hi Bill...

Not quite.  I'm really struggling to get this question straight in my own mind now.  But in a nut-shell:

Say ImgA is Dim'd as String and the value of ImgA is "PictureXYZ"  

Essentially I want to change the value of ImgA without knowing the variable name... i.e. Img & Right(folderA, 1) = "pictureXYZ" '

So I guess... this does look like a job for the dictionary but it would look more like:

ImgA => c:\foldername\filename1
ImgB => c:\foldername\filename2
ImgC => c:\foldername\filename3
ImgD => c:\foldername\filename4


Then the question becomes can I change the value of something in the dictionary by:

    file = Img & Right(somestring, 1)
    dict.Add file, filename

... to which I think the answer is yes?  I'll test soon!
ASKER CERTIFIED SOLUTION
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DrTribos

ASKER
Hi Bill, thanks for sticking with me.

Hi Sedgwick, I think your original answer was the right one but I didn't understand enough of the background.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Bill Prew

You're welcome, glad to have been of some help, thanks for the feedback.

~bp