Solved

How do I pass an Array as a function argument?

Posted on 2004-09-18
11
316 Views
Last Modified: 2012-06-21
I have created a function: HolidayList(ListID)

Using the ListID parameter, the function produces a recordset that I then use to populate an array. Then array contains dates associated with that nation's holidays.

The "HolidayList" function works. I've confirmed that the elements of the array are successfully populated and the HolidayList variable has a vartype of 8197 which I believe is an array with date elements.

The temporary variable name I assign to the array during the loop is "HL"
At the end of the function I set HolidayList = HL

I need to use the array in another function. Call this function "B"

I've tried passing the array to Function B like this:

Attempt 1:
Function B (arg1, arg2, ByRef HolidayList(1) as Variant)

Attempt 2:
FunctionB(arg1, arg2, HL as Variant)

Both return the error message: Comile Error: Typ Mismatch, array or user-defined type expected.

What do I need to do in order for the app to recognize HolidayList(1) as an array???

Having a wonderful time here saturday night...
Thanks
Bob

0
Comment
Question by:rhk6446
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12093925
Do this:

Function B (arg1, arg2, ByRef HL As Variant)

Now call it with:
Dim YourArray(5) As Date

Call B(arg1,arg2,YourArray)
0
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12094249
In most cases it is not good to make a global variable you want to limit the scope as much as you can, but if you are going to use this array in a lot of function you could create a global variable and load the information that you want. You would then have that information on hand as you needed it.
May I ask why you are using an array instead of a table to hold you data?
0
 
LVL 4

Expert Comment

by:Colonel32
ID: 12095315
Don't you need to do this?

FunctionB(arg1, arg2, ParamArray HL as Variant)

You can then call the function as shown by shanesuebsahakarn.
0
 

Author Comment

by:rhk6446
ID: 12095382
Sometimes I 'm afraid I ask such stupid questions I'm afraid of being banned from this website! I'm not a programmer, merely a financial analyst struggling with arrays.

Questions for Shane, Exaclibur & Colonel:

Shane:
Function B (arg1, arg2, ByRef HolidayList(1) as Variant)  
a) I'm OK with this

Dim YourArray(5) As Date
Call B(arg1,arg2,YourArray)

a) Do these lines reside in yet another (3rd) function, which then calls Function B?
b) How does YourArray(5) get set equal to HL ?
c) Does the "5" have any meaning, or is it just an example of a ListID?
d) What was wrong with my Attempt #1: Function B (arg1, arg2, ByRef HolidayList(1) as Variant) ? It seems this would allow me to pass the ListID and create the array all at the same time.
e) Do I achieve anything by setting HolidayList=HL at the end of the Function HolidayList ? If it does, why isn't the array named HolidayList instead of HL?

Excalibur:
a) How do I declare a Global variable? Is it "Dim HL as Global" in declarations ?
b) I'm trying to use a 3rd party function that requries the HolidayList passed as an array, the dates are actually stored in a table.

Thanks again experts,
Bob



0
 

Author Comment

by:rhk6446
ID: 12095430
Shane, Correction:
Function B (arg1, arg2, ByRef HL Variant)  
a) I'm OK with this
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12095868
rhk6446,

Don't worry, we all have to start somewhere :)

If I can clarify, this is how I'm interpreting your question. You have an array called HolidayList. You now want to pass this array to the function called B so that it can do something to that array, is that right?

In my example, I created an array called YourArray, with 6 elements (0-5). I then pass it to function B as one of the parameters in the Call statement.
0
 

Author Comment

by:rhk6446
ID: 12097365
Your interpretation is correct, however if I've already created an array (i.e. HolidayList) why do I need to create another array (i.e. YourArray) ? See point "e" above.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12097884
You don't - I was simply using YourArray as an example. In your case, you would call your function like this:
Call B(arg1, arg2, HolidayList)

To respond to a couple of your points:
d) If that is the function declaration, you are trying to declare a 2 (or 1, depending on your Option Base) element array within your function declaration, which you can't do.
e) Not really - when you pass a parameter ByRef, the function uses a pointer that points to the "real" variable, so you are effectively working with the actual variable. Any changes made to the variable are reflected in it when you exit the function. When you use ByVal, you are working with a copy of it - changes made during the function are NOT reflected when it exits.
0
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12099202
rhk6446 I am sorry if you get 3 or 4 copies of this mesage I think I have fixed the trouble now

>>How do I declare a Global variable? Is it "Dim HL as Global" in declarations ?

Create a new module on the first blank line enter the following

Public HL(5) as string

HL is the name of your array, the 5 is the number of element - 1. If you want 10 elements in your array you would use the number 9. This is because arrays are zero based.

Now you can use that array any place you want. Note this is not the best way to do your project, it is just a simple way. Shanes answer is much better.
I hope this helps you
Al
0
 

Author Comment

by:rhk6446
ID: 12108300
It runs!!!
OK, my last and final question (on this topic...)

Function B determines the next business day, after adjustment for holidays and weekends.

Something like this:
In a query I have the calculation: Function B(#12/31/2004#, HolidayList(UK) ) where UK is the country and #12/31/2004# is the current date
I expect the query to return a single record showing #12/03/2005# which is the next good business day...and it does 5137 times!

Not coincidentally, there are 5137 UK holidays for the next 30 years in my holiday table.

Aside from using a GROUP BY query which dramatically increases the runtime, is there something I can do to eliminate the extra 5136 records? I think this is related to the way that darn array is populated with the holiday list.

THE ULTIMATE THANK YOU,
Your Humble Access noob,
Bob


0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 12108320
Without looking at your function, I can't tell you exactly why this is happening. I don't know how your array is populated  - what is the SQL of the query that populates the array?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now