Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

How do I pass an Array as a function argument?

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
rhk6446
Asked:
rhk6446
  • 4
  • 4
  • 2
  • +1
1 Solution
 
shanesuebsahakarnCommented:
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
 
Excalibur_SoftwareCommented:
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
 
Colonel32Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
rhk6446Author Commented:
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
 
rhk6446Author Commented:
Shane, Correction:
Function B (arg1, arg2, ByRef HL Variant)  
a) I'm OK with this
0
 
shanesuebsahakarnCommented:
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
 
rhk6446Author Commented:
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
 
shanesuebsahakarnCommented:
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
 
Excalibur_SoftwareCommented:
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
 
rhk6446Author Commented:
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
 
shanesuebsahakarnCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now