View Full Version : Naming Array
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
06-30-2013, 07:36 AM
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
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
06-30-2013, 05:33 PM
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
Hi
Paul thanks for help. I will check & update you about its working.
Thanks
Kenneth Hobs
07-01-2013, 06:20 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.