Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I pass an Array as a function argument?

Posted on 2004-09-18
11
Medium Priority
?
326 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

916 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