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