Solved

Modify variables in VBA

Posted on 2013-05-13
21
361 Views
Last Modified: 2013-05-28
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!
0
Comment
Question by:DrTribos
  • 11
  • 7
  • 3
21 Comments
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 150 total points
ID: 39160647
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
 
LVL 14

Author Comment

by:DrTribos
ID: 39160666
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39160672
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
 
LVL 14

Author Comment

by:DrTribos
ID: 39160693
Thank you,  I'll take a look...  might be a little while before I can test / experiment
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39160698
ok man good luck
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39161059
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
 
LVL 14

Author Comment

by:DrTribos
ID: 39169942
Hi Sedgwick & Bill

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

Thanks for your patience!
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39173357
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
 
LVL 14

Author Comment

by:DrTribos
ID: 39173371
Hi Bill... um... Enum?  I can not find an Eval anywhere!
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39173598
Sorry, in VBA I believe it's called EVALUATE.

~bp
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 14

Author Comment

by:DrTribos
ID: 39173696
Hi Bill,

Can't find Evaluate either :-(
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39174595
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
 
LVL 14

Author Comment

by:DrTribos
ID: 39178520
Thanks! Have not had time to test the latest, will let you know..
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39186761
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39189582
That seems to do what you wanted, what doesn't it do that you need it to?

~bp
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39190191
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39193071
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
 
LVL 14

Author Comment

by:DrTribos
ID: 39193264
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
 
LVL 51

Accepted Solution

by:
Bill Prew earned 350 total points
ID: 39195943
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
 
LVL 14

Author Closing Comment

by:DrTribos
ID: 39201645
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 39202190
You're welcome, glad to have been of some help, thanks for the feedback.

~bp
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now