Creating arrays using dynamic array names
Hi,
I am trying to create a function that will create several arrays all from different files. I want to be able to pass the array name and file name to the function and have it create the arrayname based on what I have sent.
Here is a simplified sample
[vba]Sub GetArrays
MyArrayName = "Commision"
MyFileName = "c:\commisson.xls"
Call MakeArray(MyArrayName, MyFileName)
end sub
Function MakeArray
'open file,
' read data...
MyArrayName(i, j) = Range(data).Value
end function[/vba]
I have several files to import. Is this possible? and how do I go about doing this?
I am trying to create a function that will create several arrays all from different files. I want to be able to pass the array name and file name to the function and have it create the arrayname based on what I have sent.
Here is a simplified sample
[vba]Sub GetArrays
MyArrayName = "Commision"
MyFileName = "c:\commisson.xls"
Call MakeArray(MyArrayName, MyFileName)
end sub
Function MakeArray
'open file,
' read data...
MyArrayName(i, j) = Range(data).Value
end function[/vba]
I have several files to import. Is this possible? and how do I go about doing this?
ASKER
Thanks for the response Cluskitt.
Still wondering if it is possible to have the MAkeArray function, where the Array name is a dynamic. That is my key question.
What you want is something like:
Sub GetArrays
Dim MyArrayName1, MyArrayName2 As Variant
MyFileName = "c:\commisson.xls"
MyArrayName1 = MakeArray(MyFileName)
MyFileName = "c:\commisson2.xls"
MyArrayName2 = MakeArray(MyFileName)
end sub
Function MakeArray(nameVar As String) As Range
'open file,
' read data...
Return Range(data)
End Function
Basically, the function will take 1 argument, a string, that contains the filename. It will then perform whatever operations you want, and return the data range. It is then assigned to whatever array you want.
Sub GetArrays
Dim MyArrayName1, MyArrayName2 As Variant
MyFileName = "c:\commisson.xls"
MyArrayName1 = MakeArray(MyFileName)
MyFileName = "c:\commisson2.xls"
MyArrayName2 = MakeArray(MyFileName)
end sub
Function MakeArray(nameVar As String) As Range
'open file,
' read data...
Return Range(data)
End Function
Basically, the function will take 1 argument, a string, that contains the filename. It will then perform whatever operations you want, and return the data range. It is then assigned to whatever array you want.
ASKER
Thanks again, that makes sense. The problem is that I don't know how many tables will be imported at any one time. Is it possible to then have this so that imports all the tables without having to adjust the code each time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use a COLLECTION where you add the Arrays to the Collection using the Name as the Key:
http://msdn.microsoft.com/en-us/library/aa242681(VS.60).aspx
http://msdn.microsoft.com/en-us/library/aa265006(VS.60).aspx
So you'd declare just ONE Collection to hold the dynamic number of arrays.
http://msdn.microsoft.com/en-us/library/aa242681(VS.60).aspx
http://msdn.microsoft.com/en-us/library/aa265006(VS.60).aspx
So you'd declare just ONE Collection to hold the dynamic number of arrays.
Can't do this. What you can do is:
MyArrayName = Range(data)
This will create a 2 dimension array (for rows and columns). You can always check the size with Ubound.
Also: MyArrayName = "Commision"
This will effectively set MyArrayName as a single variable of String type. Either don't declare it, or declare it as variant (which it needs to be to get a range assigned to it anyway).