VBA, Variable Resolves to another Variable

A collection would be another storage mechanism in VBA that might meet your need.

~bp

Basically, I have global constant strings that contain the names of output files from SAS programs.  Depending on the model that was run, these files may be named differently (not my doing).  Thus, I need to detect which model was run via user input, and open the appropriate file.

E.g.  
2 constants
Public Constant LOREAN_MODEL_FILE_NAME As String = "summary_stats"
Public Constant SCOLORE_MODEL_FILE_NAME As String = "model_statistics"

In the code I can detect which model was selected (LOREAN or SCOLORE).  I would have liked to concatenate the model type with the rest of the variable name for the file, and been able to use that to get the name of the file I need.

I was thinking of doing one of two things:

1. create some more global constants to store the values of the file names for the currently selected model

2. Insert conditional statements to set the appropriate file name in the modules that open the files.

1. requires more global variables, which essentially are copies of others, while 2. requires redundant code in multiple locations.  I'm leaning towards 1.

I would be interested in hearing how a collection might be used for this purpose.  As for your array idea, I've never tried storing constants in an array.  I suppose they don't have to be declared on constants.  The other thing is I'd have to come up with unique identifiers or else I'd have to loop through the array to search for the model - file combination.

Let me know if you have any ideas, otherwise, I'll probably just close this with your first solution accepted as the answer, since it isn't possible to do what I have asked.

WC

Just thought of a better way around this.

I'm just going to create a custom function that is given the MODEL and FILE as parameters and will return the appropriate global constant.

Thanks for your time.

Cheers,
WC

Not possible, so creating a custom function.

Depending on your motivation, you might be able to use the undocumented VarPtr, StrPtr and ObjPtr functions in VBA. These are documented at http://vb.mvps.org/tips/varptr.asp and their use is illustrated at http://www.codeproject.com/KB/vbscript/UB_Pointers_In_VB.aspx

A different approach, using completely documented VBA stuff, is to set up an enum array. You can address the elements of this array by either their name or index number.

Brad

Brad,

I've used StrPtr in the past in conjunction with an InputBox in order to determine whether the user hit Cancel or pressed Ok without entering anything, thus detecting the difference between empty string and vbCancel.

Seeing as my early programming was in C++ it's nice to know that you can use pointers in VB, so thank you for the links.  I'm having a little trouble implementing this though.  It keeps crashing Excel.

I have tried using the Declare statement from one of the links you provided, which in turn provided another link.

Public Declare Sub CopyMemoryRead Lib "kernel32" Alias _
    "RtlMoveMemory" (Destination As Any, _
    ByVal Source As Long, ByVal Length As Long)

I then put the following in one of my macros.

    Dim fileName As String
    Declarations.CopyMemoryRead fileName, StrPtr(TempType & "_MODEL_FILE_NAME"), 500

TempType stores the model type (LOREAN, SCOLORE, etc.)

It's my understanding that the Declare Sub will store the value passed by the second argument into the memory location of the first, thus it should store the value of my global constant string into fileName, correct?

WC

Ok, so I figured I need to be writing to the variable fileName, not reading from it, so I am using the other declare statement now,

Public Declare Sub CopyMemoryWrite Lib "kernel32" Alias _
    "RtlMoveMemory" (ByVal Destination As Long, Source As Any, _
    ByVal Length As Long)

but Excel is still crashing on the line

    Declarations.CopyMemoryWrite VarPtr(fileName), StrPtr(TempType & "_MODEL_FILE_NAME"), 500

The following has returned put the string "ModelStatistics" into fileName on occasion, but more times than not I just get a string of ?, "?????????????".  It also crashes still sometimes, and I cannot change the variable either.

    Declarations.CopyMemoryWrite StrPtr(sheetName), StrPtr(TempType & "_MODEL_FILE_NAME"), Len(TempType & "_MODEL_FILE_NAME") * 2


Also, this is not evaluating to the value stored in SCOLORE_MODEL_FILE_NAME, but just the string "SCOLORE_MODEL_FILE_NAME".

What I mean is that,
StrPtr(SCOLORE_MODEL_FILE_NAME)
and
StrPtr(TempType & "_MODEL_FILE_NAME")

return 2 different memory addresses when TempType is "SCOLORE".

I found the following link, but I'm unable to get the function mentioned in it to work either.

http://www.vbforums.com/showthread.php?t=112946

Really strange what's going on.  I can't figure it out.

I just wanted to see if it would work if I hardcoded the variable names in.  It still is pretty sketchy.

    Declarations.CopyMemoryWrite StrPtr(sheetName), StrPtr(LOREAN_MODEL_SHEET_NAME), Len(LOREAN_MODEL_SHEET_NAME) * 2


This cycles through 4 different results.  The first two times I execute this line of code, it returns "ModelStatistics", which isn't correct, it should be "summary_stats".
The third time it returns "???????????????" and the fourth, "?????????????????"

It then cycles back to the first one.

Ok, I changed the first argument,

   Declarations.CopyMemoryWrite VarPtr(sheetName), StrPtr(LOREAN_MODEL_SHEET_NAME), Len(LOREAN_MODEL_SHEET_NAME) * 2

which is now a VarPtr.  This returned "summary_stats", HELL YEA!!  Unfortunately, running this a second time crashed Excel.  Also, it doesn't solve the problem of how do I get this to work with the variable string that stores which model type.

 Declarations.CopyMemoryWrite VarPtr(sheetName), StrPtr(TempType & "_MODEL_SHEET_NAME"), Len(TempType & "_MODEL_SHEET_NAME") * 2

Obviously that Len() will not work the way I want.  It returns the length of that concatenated string, not the string contained in that global constant.