Solved

How do I pass an Array as a function argument?

Posted on 2004-09-18
11
315 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Shane, Correction:
Function B (arg1, arg2, ByRef HL Variant)  
a) I'm OK with this
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

11 Experts available now in Live!

Get 1:1 Help Now