VBA- How to point string to array value

[Mr.FutureCoder]

Hi,
As shown below, What I am trying to is

sub array1()

Dim sName as string
Dim commodity(3) as string
Dim NG_DJ(3) as string


commodity(1)="NG"
commodity(2)="CL"
commodity(3)="HO"

NG(1) = "03": NG(2) = "05": NG(3) = "07"
CL(1) = "01": CL(2) = "02": CL(3) = "03"
HO(1) = "06": HO(2) = "08": HO(3) = "10"

'**Here is the problem**'

sName= commodity(1) (1)

end sub

Output of the sName should be "03" as commodity(1) is "NG" and then it should take NG(1) and retrieve the "03". Any help would be appreciated highly. Thanks.

 

[Weaver]

You'd need to set your arrays up like this:

commodity(1)=array("03","05","07")

 

[Mr.FutureCoder]

Thanks for the reply. Actually I have 20 commodities for which I have assigned the commodity() array. Everytime my commodity gets changed so at the same time I want to change the the array of that commodity which points towards the month number. Can I do it in some way or other? Thanks.

 

[Rick Rothstein]

You can stick with your original "structure" and still use the assignment Weaver showed you, but you need to read the note after the code...

Sub Array1()
  Dim sName As String
  Dim Commodity(1 To 3) As Variant
  Dim NG(1 To 3) As String
  Dim CL(1 To 3) As String
  Dim HO(1 To 3) As String
 
  NG(1) = "03": NG(2) = "05": NG(3) = "07"
  CL(1) = "01": CL(2) = "02": CL(3) = "03"
  HO(1) = "06": HO(2) = "08": HO(3) = "10"
 
  Commodity(1) = Array(NG(1), NG(2), NG(3))
  Commodity(2) = Array(CL(1), CL(2), CL(3))
  Commodity(3) = Array(HO(1), HO(2), HO(3))
 
  sName = Commodity(2)(0)   'Retrieves the value of "01"
 
  '  Show the assignment
  MsgBox sName
 
End Sub

NOTE: The Array function used in assigning the array of values to each element of Commodity uses the Option Base setting from the computer running the code. The default Option Base is 0 meaning if a lower bound for an array is not manually set, then it defaults to 0. If the Option Base is set to 1, then arrays without a manually set lower bound will be 1. You cannot manually set the lower bound for arrays created by the Array function, so the index number you would use in the second parentheses depends on your Option Base setting. I'm assuming you do not have an Option Base 1 statement in your code, so the first element of the second parentheses element would be 0, not 1 (but because we manually set the lower bound for the other arrays to 1, then those elements start counting from 1). So, for the settings shown in the code, you would obtain the "01" value from the CL array like this...

Commodity(2)(0)

 

[Mr.FutureCoder]

Thanks Mr. Rothstein. But if I assign array directly to commodity(), then what if I need to use commodity name, for example "NG" i.e. commodity(1), in the code?

I am using commodity() array for assigning names of commodity to different fields in a loop. So if I use & commodity(2) should take "CL" string in account. and then if I use & commodity(2)(0) then "01".

 

[Norie]

The array Commodity is just an array of 3 strings.

You can't use those strings to access items in other arrays.

Arrays don't work like this in VBA.

 

[Mr.FutureCoder]

I was dying to hear this. Thanks Norie.

 

[Norie]

Sorry, perhaps you could explain what you are trying to do someone can suggest an alternative.

 

[Mr.FutureCoder]

Thanks for asking Norie but I altogether changed my approach towards my code and so problem is solved for now. However I was wondering if it would be possible to read strings from one array and use it as array name to call values of that array. I know I am asking for impossible/imaginative thing but still i believe ther is always scope for improvement either in me or the VBA itself .

 

[Norie]

You want to use a value from an array to refer to other values in the same array?