Naming Array

[xls]

Hi

I want to create 5 array & assign its name as value stored in range A1 to A5.

EG. Values in A1 to A5 is CAT,BOB,BEN,VBA, XLL.

So i want to create array by name CAT,BOB,BEN,VBA, XLL.

How to do this. Pl help, thanks in advance.

[Paul_Hossler]

Doubt you can do it (be interesting to see if some one has an idea, tho)

Why do you want to do that?

I assume that you were looking for something equivalent to

Dim CAT(1 to 100) as long, BOB (1 to 100) as Long etc.??


Paul

[xls]

Thanks for quick response.

you are perfectly right.

I want to create, Dim CAT(1 to 100) as long, BOB (1 to 100) as Long etc.??

However i dont know whether it would be CAT or BOB.

I want to create array with its name as name stored in values in range.

Then i want to pass value to values to array.

Thanks

[Paul_Hossler]

well ... this is sort of close to what you're thinking

It uses a collection with the key = your names and the data in an array

You refer to the collection entry using the name (key)


Option Explicit
Sub test()
Dim collArray As Collection
Dim rData As Range, rForArray As Range, rRow As Range
Dim sKey As String
Dim vData() As Variant
Dim i As Long

Set collArray = New Collection

For Each rRow In ActiveSheet.Cells(1, 1).CurrentRegion.Rows
With rRow
sKey = .Cells(1, 1).Value

Set rForArray = Range(.Cells(1, 2), .Cells(1, 2).End(xlToRight))
ReDim vData(1 To rForArray.Columns.Count)

For i = LBound(vData) To UBound(vData)
vData(i) = rForArray.Cells(i)
Next i


Call collArray.Add(vData, sKey)

End With
Next
MsgBox collArray("BOB")(1)
MsgBox collArray("BOB")(3)
MsgBox collArray("BOB")(5)

For i = LBound(collArray("XLL")) To UBound(collArray("XLL"))
MsgBox i & " ---- " & collArray("XLL")(i)
Next

Stop
End Sub


Paul

[xls]

Hi

Paul thanks for help. I will check & update you about its working.

Thanks

[Kenneth Hobs]

You could do it with vbcomponents but I have not seen a need to do that. http://www.cpearson.com/excel/vbe.aspx

e.g.
Sub Test()
'Dim the_Calc As String
'the_Calc = "Range(""B13"").Formula = ""=SUM(D12:D14)"""
'CommandAsString the_Calc
CommandAsString Range("B5").Value
End Sub

'For other tips, see Chip Pearson's, http://www.cpearson.com/excel/vbe.aspx

Sub CommandAsString(cmdString As String)
'Add: Tools > Reference > Microsoft Visual Basic for Applications Extensibility 5.3 > OK
Dim MyComponent As VBComponent
Set MyComponent = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)

Dim MyCodeString As String: MyCodeString = "Public Sub VBACommandAsString" & vbCrLf
MyCodeString = MyCodeString & cmdString & vbCrLf
MyCodeString = MyCodeString & "End Sub" & vbCrLf

MyComponent.CodeModule.AddFromString MyCodeString

Application.Run "VBACommandAsString"

'Debug.Print ThisWorkbook.VBProject.VBComponents.Count

ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents.Item(MyComponent.Name)
End Sub