how pass array to a function?

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[Chip Pearson]

Do not pass an array!

And just why is that?

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

 

[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

 

[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

 

[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

 

[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

 

[Chip Pearson]

This isn't much of anything in the Help file. See
http://www.cpearson.com/excel/PassingAndReturningArrays.htm and
http://www.cpearson.com/excel/ReturningArraysFromVBA.aspx
for some notes that might prove helpful.

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

 

[John]

Thanks. Pretty complicated but I got it to work.
John