variable variable name 2

[LuAlPa]

Ok, here is the deal.
User picks up to item 4 items form an iterative InputBox.
He may actually pick less than 4.
I store choices in OptVal(4).
Then I access a mainframe data base and based on choices resolve user choices.
Result would be to paste to an Excel cell the returned value from the mainframe data base.

Issue: In OptVal(i) I have the different values the user entered (the name of the data he/she needs) but when I load in a variable WITH THE SAME NAME AS THE CUSTOMER CHOICE VBA will not have that variable with that data but only the original choice from the user.
Bottom line, I need a way to use variable variable names.
Hope this is clear. (But I doubt it)

 

[TonyJollans]

What sort of variables are you using? Are the users having to enter the names of variables in your code? Or are they names on the mainframe, or what?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[LuAlPa]

The data I am offering are different dates or pieces of data the user can choose from. These are service order that within my company have several dates or statuses. So he chooses like "Entered" "Limit" "Minimum" "Current Status" "Last owner" "Origin" (So basically he gives me the name of the data base field he needs for a list of orders in an Excel column.)
So, for example, he picks up ORIGIN which I store in an element of the array along with LIMIT in another.
When I access the data base record for the service order I just read the whole record with the 27 items it has and load in variables (just to make things faster)
So I have ORIGIN data , say, "UK" (regardless of whether the user has chosen it or not) and LIMIT is "10/30/2008"
But before reading the next order in the Excel column I wan to paste ORIGIN and LIMIT data to the right of the column.
activecell.offset(0,1) = ORIGIN
activecell.offset(0,2) = LIMIT
will do the trick, yest, but the issue is that I do not know what has been chosen since that data is in the array and I do not what to do a select case per each piece of data I have to paste ---- and the user may chose in any order
activecell.offset(0,1) = OptVal(1)
activecell.offset(0,2) = OptVal(2)
do not work. These physically paste the words ORIGIN and LIMIT.

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TonyJollans]

I think you're making it hard for yourself; you're trying to do the job of the mainframe DMBS (DB2? Oracle?). And performance should not (at least in theory) be an issue for application code.

You have the field names which, I presume, are column names - build a proper SQL statement to read what you want and the rest should be easy.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[PHV]

If you use a Recordset to retrieve the data:
ActiveCell.Offset(0,1) = rs.Fields(OptVal(1)).Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LuAlPa]

ok, my fault, Tony. I was actually being a bit misleading. We, mere mortals at a product department level, are not allowed to run a direct, real-time query against the MF database (Ideal Datacom, actually, running under CICS / OS/390 environment). So we screen-scrape a Hummingbird front-end and my data is retrieved by screen coordinates (the interface is 3270-look alike and the screen is nothing but a 1920 bytes string).

PH, I am investigating into Recordsets. I had never heard of them. Compatible with MS Visual Basic 6.5 and Excel 2003 SP3? ANy special definition or DIM I should be aware of? How do you define/DIM your rs in the example above?

 

[TonyJollans]

Difficult without seeing the data, but ...

You must be parsing the CICS screen somehow. If each field is always at the same position in the buffer (possibly a big if, I know - it depends how the screen scraper works, but if you always get a 1920-byte string it's probably true) you should be able to translate your (user entered) field names into start and end positions and pick each one directly from the buffer - rather than extracting all fields and then trying to match field names as you sound like you're doing at the moment

If not, you're going to need a big Select Case statement I suspect.

I hope that makes sense!

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[LuAlPa]

IT MAKES ABSOLUTE SENSE. And that is exactly what I am doing at this time. Propose the data to the user but getting ALL data from the buffer (that is the esasy part). What I do not want to do is that big SELECT. I have the name of the field the use wants in my array but I can not seem to translate that into a data field in my VBA code. The recordset definition is not accepted by VBA. That is qhy I am aksing if there is any special DIM I have to do.

 

[TonyJollans]

There is no 'special Dim' you can use, but I'm not sure you have completely understood what I have said.

Even assuming you could use an indirect variable, how would you have populated your variable 'Origin', say, when all you got from the mainframe was a 1920-byte array?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[PHV]

I'd use a Scripting.Dictionary object:

http://msdn.microsoft.com/en-us/library/x4k5wbx4(VS.85).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LuAlPa]

Since I have all the coordinates, I populate the variables with no problem. ORIGIN = trim(myBuffer(12,3,3))

 

[PHV]

Did you make sense with my suggestion ?
You may use a Dictionary as an associative array ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LuAlPa]

Will see into the dictionary proposal shortly. Another thing I had not heard of. Thank you PHV.

 

[PHV]

You may also consider a Collection in VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TonyJollans]

The point I was trying to make was that you have the coordinates - and you use them to set all the variables, so you must already have a lot of code addressing each of the elements individually. If the user asks for origin, say, all you want to pull from the buffer is the origin - rather than extracting everything and the trying to match variable names against user input.

An associative array (or maybe two), as PH suggests, is a way of holding a lookup table so you can reference (pseudocode) startpos('origin'), for example, rather than having to code a select case structure or similar. Personally I'd be inclined to create my own mainframe class with methods for all the variables - but that would be more work.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[LuAlPa]

Tony & PH, thank you for the pointers.
Between collections and associative arrays we (with my techy guru) will make it happen.
Now i will have less time to code since I am in a production environment-- unlike during the weekend.

Thank you again
Lp