Solved

Creating arrays using dynamic array names

Posted on 2011-02-21
6
253 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:kuda27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 34943162
MyArrayName(i, j) = Range(data).Value
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).
0
 

Author Comment

by:kuda27
ID: 34943202

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.  
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 34943286
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.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:kuda27
ID: 34944015
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.
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 500 total points
ID: 34944254
That would depend on how you're loading them. But what you basically need is to build a For, While or Repeat loop. If you want each table loaded in a different array, you can just create an array of arrays. Something like:

Dim MyArrayName(1 To 2) As Variant
ReDim MyArrayName(1 To TablesCount) 'TablesCount is a variable with the number of tables
For x=1 To TablesCount
  'Read whatever filename you want
  MyArrayName(x) = MakeArray(MyFileName)
Next


You can then access each like this:

MyArrayName (5) (3,2)  'this will get the the 3rd line and 2nd column in the 5th array/table
 
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 34944324
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.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to line 23 79
Collapse and expand table in Word 2010 2 154
all the possible sequences for numbers 1,2,3,4,5,6,7,8,9,10 in groups of 5. 5 62
T-SQL Query 9 34
This is an explanation of a simple data model to help parse a JSON feed
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question