how pass array to a function?

J

John

If you have an Array, say Array(9), how do you pass that information
for a function to use?

For example:
Public Function MyFunction(x as long) as Boolean
Dim n as Long
MyFunction=False
For n = 1 to 9
If Array(n) = x then MyFunction = True
Next
End Function

I know you can't have Public Function MyFunction(x as Long, Array() as
long) as Boolean"

But there has to be some way for public functions to access arrays
outside the function doesn't there?

Thanks

John
 
D

Dennis Tucker

Do not pass an array!

The key here is the "Scope of Variables".

At the top of the module, not inside any Sub or Function, put the
declaration of the array.

Dim MyArray(3,35) as string (just an example)

Now MyArray() has a wider scope. The scope in this case is the current
module(or userform).

For even wider scope use this

Public MyArray(3,35) as string (just an example)

The scope in this case is the current project(all userforms and modules
inside the current project.

Dennis
 
J

John

I sort of assumed I had dimmed the array and filled it with values
before the function.

So: Dim Array(9) as long
Dim n as long
For N = 1 to 9
Array(n)=n
Next

The following creates and error. Also if i just use the Array(9) in the
function it creates and error.

Public Function MyFunction(x as long,Array() as long) as Boolean
Dim n as Long
MyFunction=False
For n = 1 to 9
If Array(n) = x then MyFunction = True
Next
End Function

I'm not sure if what you suggested gets around that. Or are you saying I
just can't include that array inside the function period?

John
 
J

Jacob Skaria

Hi John

Check out the below procedure/function

Sub Macro()
Dim myArray() As Long
Dim n As Long

For n = 1 To 9
ReDim Preserve myArray(n)
myArray(n) = n
Next

MsgBox MyFunction(3, myArray)
End Sub

Public Function MyFunction(x As Long, varArray As Variant) As Boolean
Dim n As Long
For n = 1 To UBound(varArray)
If varArray(n) = x Then MyFunction = True: Exit For
Next
End Function
 
D

Dana DeLouis

For n = 1 to 9
If Array(n) = x then MyFunction = True
Next

Hi. Would this idea help?

Sub TestIt()
Dim v
v = Array(1, 2, 3, "Cat", 4, 5)

Debug.Print MemberQ(6, v) 'Is 6 in v?
Debug.Print MemberQ(4, v)
Debug.Print MemberQ("Dog", v)
Debug.Print MemberQ("Cat", v)
End Sub

Returns:
False
True
False
True

Function MemberQ(x, M) As Boolean
'// Is x a member of Array M ?
On Error Resume Next
MemberQ = WorksheetFunction.Match(x, M, 0) > 0
End Function

= = = = = = = = = =
HTH :>)
Dana DeLouis
 
B

Bob Phillips

You don't need a function, just use

If Not IsError(Application.Match(x,Array,0)) Then

'do something

But you can pass an array in a Function like so

Public Function MyFunction (x As Long, ary As Variant)
Dim n As Long

For n = LBound(Ary) To UBound(ary)

If x = Ary(n) Then

MyFunction = True
Exit Function
End If
Next n
End Function

HTH

Bob
 
J

John

Yeah... have to explain more I guess. A number of times in the program
I'm writing I need to see if a a particular cell is a member of the
union of three different arrays. So I did the following:

Set Union1 = Application.Union(Array1(3),Array2(5),Array3(11))

Each array is an array of arrays (so to speak). The "3", "5" and "11"
would be indicated by variables.

Then, to see if a particular cell value (Say RangeToCheck) is in Union1
I just do:

Select Case Application.Intersect(RangeToCheck, Union1) Is Nothing
case true... etc
case false... etc


Looks great but the application.intersect just doesn't work. So now
instead of all that I could just use match or countif or a number of
other things every time but I would rather have a function then use 3
searches every time.

So... I want to pass all three arrays to the function along with the
RangeToCheck and get back a true or false.

John
 
J

John

I am not familiar with the redim preserve myArray

Also not familar with the varArray as Variant.

Guess I am slowly learning here.
John
 
D

Dennis Tucker

Chip,

As far as I know, arrays can not be passed. A reference to the array can be
passed but just not the array itself.

Is that correct?

Dennis
 
D

Dana DeLouis

Select Case Application.Intersect(RangeToCheck, Union1) Is Nothing
case true... etc
case false... etc


Looks great but the application.intersect just doesn't work.


Hi. I wouldn't do it this way, but here is my guess.
I believe your example is not working because you are not setting the
intersection as an object. Step thru the Demo1, and note that x is
"Empty", even though it should be a valid range object.

Now, try Demo2, and you will see that x is a valid range object, and can
now be tested.


Sub Demo1()
Dim x
x = Application.Intersect([B1], [B1:B10])
End Sub


Sub Demo2()
Dim x
Set x = Application.Intersect([B1], [B1:B10])

If x Is Nothing Then
Debug.Print "Nothing"
Else
Debug.Print "Valid"
End If

End Sub

= = = = = = =
HTH
Dana DeLouis
 
C

Chip Pearson

You are correct that arrays are always passed byref. That's why your
comment seemed strange -- you were warning not to do something that
can't be done.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
J

John

While I have found another solution to my current problem, I still don't
know how to use an array in a function. Is it explained in the excel
help in 2007?
John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top