DrTribos
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"]
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"]
ASKER
Thank you, I'll take a look... might be a little while before I can test / experiment
ok man good luck
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
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
ASKER
Hi Sedgwick & Bill
Sorry I have not had a chance to test this. Hope to do it today.
Thanks for your patience!
Sorry I have not had a chance to test this. Hope to do it today.
Thanks for your patience!
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):
Modified (object required):
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
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
ASKER
Hi Bill... um... Enum? I can not find an Eval anywhere!
Sorry, in VBA I believe it's called EVALUATE.
~bp
~bp
ASKER
Hi Bill,
Can't find Evaluate either :-(
Can't find Evaluate either :-(
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.
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
~bp
ASKER
Thanks! Have not had time to test the latest, will let you know..
ASKER
Hi Bill,
I don't think this will do what I asked... I understand the code a bit better, changed it to:
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,
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
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,
That seems to do what you wanted, what doesn't it do that you need it to?
~bp
~bp
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,
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,
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
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
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Hi Sedgwick, I think your original answer was the right one but I didn't understand enough of the background.
You're welcome, glad to have been of some help, thanks for the feedback.
~bp
~bp
ASKER
I am not sure how this will impact on my project as some of the variables are global.