Indirect Reference to a Variable in VBA

[Arul.rajesh]

I have a list of values which are names of arrays

Something like

 CDS=Array(CIPR,CIPW,PTPA,CSRP,CITR,CPER,CPCT,CPSC,CW9D,VMLE,VMAD,AUTH,AUTE,AUTB,AUTL,NMRC,NMRE,NMRF,NMFR,CDTF,DTFA,CDTE,CNOF,CFWA,CDED,CDES,DEDR,CCOB,PEOB,CCOD,BMAX,MAXS,CNCS,CNPI,CFAX,CAPP,DEFA,CPEE,CPFL,CFWU,CFWE,CCAP,AUTW,CITW,CIPE,CDUP,CPNL,BMBS,ADDI,POSR,CLIC,CLIA,COIN,HOSP,OONP,POST,PPMX,PPMD,PROV)

Each four digit string is an array.

The user selects an option from a ComboBox

I need to Set the value of a another variable to an element in the array referenced by the "CODE" the user selects.

The problem is the selected text is a string.

I can loop through all the codes and check if the user has selected which "CODE", if I have the name of the array as the first element in the array.

 

[Arul.rajesh]

simply put


need the value in a variable is the value of another variable

 

[vds1]

What is user selecting in ComboBox ? Can you please provide me one example with data?

 

[Arul.rajesh]

What is user selecting in ComboBox ? Can you please provide me one example with data?

The user selects an option like this I can get the four letter code by
<a href="http://www.imagehousing.com/image/872518"><img border="0" src="http://img1.imagehousing.com/51/9045c9ebb370d11fc1439b37354498ff.jpg" alt="Free Image Hosting"></a><br><a href="http://www.imagehousing.com/image/872518">Free Image Hosting</a><br>

code=left(codebox.text,4)

now CPER=Array("dos","DSO",1,"Ins Name")

but I want the array CPER if the user selects CPER in the above example not the string CPER

 

[Arul.rajesh]

Let me rephrase my question

Like the way indirect function works in EXCEL

I need a VBA indirect function which would reference the variable stored in the value of another variable.

arg1=array(2,3,4)

arg2="arg1"

n=VBAINDIRECT(arg2) should assign the array arg1 to n

I need that VBAINDIRECT function

 

[Arul.rajesh]

Valko help...

 

[vds1]

you can try this,

code = Left(codebox.Text, 4)
 
Select Case code
   Case "CPER": CPER = Array("dos", "DSO", 1, "Ins Name")
   'Case "CPCT": CPCT = Array("data1", "data2", "data3", "data4")
 End Select

By default all those array variables would be variant.

Hope this helps!

 

[Andrew Poulsom]

Sorry, you have to be explicit when using variables in VBA. You can't use strings in their place and there is no equivalent of Excel's INDIRECT.

 

[Arul.rajesh]

you can try this,

code = Left(codebox.Text, 4)
 
Select Case code
   Case "CPER": CPER = Array("dos", "DSO", 1, "Ins Name")
   'Case "CPCT": CPCT = Array("data1", "data2", "data3", "data4")
 End Select

By default all those array variables would be variant.

Hope this helps!

Perfect, Actually that even saves me assigning the arrays in the beginning of the SUB. Thanks a Lot.

I am from Hyderabad

Maybe we could catch up at the Metallica concert at the end of this month in Bangalore, I will be there. Ping me at arul.rajesh"at"gmaildotcom

 

[Arul.rajesh]

Sorry, you have to be explicit when using variables in VBA. You can't use strings in their place and there is no equivalent of Excel's INDIRECT.

Hmmm.. there must be a workaround.