Solved

How do I pass an Array as a function argument?

Posted on 2004-09-18
11
324 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
[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
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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
 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

630 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