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

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" wrote in message
...
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

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

Dennis Tucker wrote:
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" wrote in message
...
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

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


--
Jacob


"John" wrote:

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

Dennis Tucker wrote:
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" wrote in message
...
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

.

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


On 3/19/2010 1:44 AM, John wrote:
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

Dennis Tucker wrote:
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" wrote in message
...
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



--
= = = = = = =
HTH :)
Dana DeLouis

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" wrote in message
...
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

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






Bob Phillips wrote:
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" wrote in message
...
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

I am not familiar with the redim preserve myArray

Also not familar with the varArray as Variant.

Guess I am slowly learning here.
John


Jacob Skaria wrote:
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

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



On Thu, 18 Mar 2010 22:31:47 -0700, "Dennis Tucker"
wrote:

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" wrote in message
...
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

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

"Chip Pearson" wrote in message
...

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



On Thu, 18 Mar 2010 22:31:47 -0700, "Dennis Tucker"
wrote:

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" wrote in message
...
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

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


On 3/19/2010 2:24 PM, John wrote:
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






Bob Phillips wrote:
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" wrote in message
...
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





--
= = = = = = =
HTH :)
Dana DeLouis

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


On Fri, 19 Mar 2010 18:15:30 -0700, "Dennis Tucker"
wrote:

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

"Chip Pearson" wrote in message
.. .

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



On Thu, 18 Mar 2010 22:31:47 -0700, "Dennis Tucker"
wrote:

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" wrote in message
...
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

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


On Fri, 19 Mar 2010 18:15:30 -0700, "Dennis Tucker"
wrote:

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

"Chip Pearson" wrote in message
...
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



On Thu, 18 Mar 2010 22:31:47 -0700, "Dennis Tucker"
wrote:

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" wrote in message
...
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

This isn't much of anything in the Help file. See
http://www.cpearson.com/excel/Passin...ningArrays.htm and
http://www.cpearson.com/excel/Return...ysFromVBA.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




On Sat, 20 Mar 2010 14:27:34 -0700, John wrote:

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


On Fri, 19 Mar 2010 18:15:30 -0700, "Dennis Tucker"
wrote:

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

"Chip Pearson" wrote in message
...
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



On Thu, 18 Mar 2010 22:31:47 -0700, "Dennis Tucker"
wrote:

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" wrote in message
...
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

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

Chip Pearson wrote:
This isn't much of anything in the Help file. See
http://www.cpearson.com/excel/Passin...ningArrays.htm and
http://www.cpearson.com/excel/Return...ysFromVBA.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




On Sat, 20 Mar 2010 14:27:34 -0700, John wrote:

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


On Fri, 19 Mar 2010 18:15:30 -0700, "Dennis Tucker"
wrote:

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

"Chip Pearson" wrote in message
...
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



On Thu, 18 Mar 2010 22:31:47 -0700, "Dennis Tucker"
wrote:

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" wrote in message
...
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