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!
LVL 15
DrTribosAsked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
Yes, this feels like a use for a dictionary.  Just adjust your thinking from needing named variables to store the paths.  You can use the dictionary for that storage and access as needed.

Actually, the Add method only works if the key doesn't already exist.  If it already exists, then the Key property can be used to change an existing keys associated value.

Here's a decent explanation that might help, but I'm happy to help create code if you want to share further details of exactly what you need.

http://windowsitpro.com/scripting/understanding-vbscript-dictionary-object-alternative-arrays

~bp
0
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
use dictionary instead of variable for each image type:
Dim dict As New Scripting.Dictionary 
dict.Add right(filename, 3) , thisdocument.path & filename

Open in new window

so u ended up having a map between the image type and the path to the file:
dict["jpg"] = "....file.jpg"
dict["bmp"] = "....file.bmp"
and so on.
0
 
DrTribosAuthor Commented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Meir RivkinFull stack Software EngineerCommented:
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"]
0
 
DrTribosAuthor Commented:
Thank you,  I'll take a look...  might be a little while before I can test / experiment
0
 
Meir RivkinFull stack Software EngineerCommented:
ok man good luck
0
 
Bill PrewCommented:
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
0
 
DrTribosAuthor Commented:
Hi Sedgwick & Bill

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

Thanks for your patience!
0
 
DrTribosAuthor Commented:
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

0
 
DrTribosAuthor Commented:
Hi Bill... um... Enum?  I can not find an Eval anywhere!
0
 
Bill PrewCommented:
Sorry, in VBA I believe it's called EVALUATE.

~bp
0
 
DrTribosAuthor Commented:
Hi Bill,

Can't find Evaluate either :-(
0
 
Bill PrewCommented:
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
0
 
DrTribosAuthor Commented:
Thanks! Have not had time to test the latest, will let you know..
0
 
DrTribosAuthor Commented:
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,
0
 
Bill PrewCommented:
That seems to do what you wanted, what doesn't it do that you need it to?

~bp
0
 
DrTribosAuthor Commented:
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,
0
 
Bill PrewCommented:
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
0
 
DrTribosAuthor Commented:
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!
0
 
DrTribosAuthor Commented:
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.
0
 
Bill PrewCommented:
You're welcome, glad to have been of some help, thanks for the feedback.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.