Instead of Sheets(SN).Cells(i + Row, j + Col)
try Table.Parent.Cells(i + Row, j + Col)
or Table.Cells(i, j) should give you the same result.


You might also try
zarray = Table.Value
after you've dimensioned zarray instead of using the nested loops.

Also, it looks like your zarray is base 0 (which is the default unless
you've used Option Base 1 at the top of your module), so it may have one more
row and column than the range you want to put into it. You can force zarray
to use Base 1 (so the row and column numbers of your array correspond to the
row and column numbers of your Table) using one of the following:

ReDim zarray ( 1 to norows, 1 to nocols)
or put

Option Base 1
at the top of your module

or just use Base 0 and make other necessary corrections
ReDim zarray ( norows-1, nocols-1)

Then zarray(0,0) will correspond to Table.Cells(1,1).



"RPH" wrote:


I've put together a VBA worksheet function that has a range (called
TABLE) passed as one of the parameters.
That range is then entered into an array using loops. I've got this to
work OK if the range data is in the same sheet as I'm using the
function.
Say I'm in Sheet1 of my workbook and the data for TABLE is on Sheet2
I'd like to be able to input
=TEST('Sheet2'!A1:F10)
VBA doesnt seem to like this
I got round it by adding the parameter SN to allow the range data to be
on a different sheet. That has to be manually input in inverted commas
right now.
So my function now looks like:-

Function Test(SN As String, Table As Range)

Dim zarray() As Single '
Dim norows As Integer ' number of rows in the array
Dim nocols As Integer ' and the number of columns
Dim Row As Integer ' Will be the first row of the range
Dim Col As Integer ' will be the first column of the range

' Establish the number of rows and columns in the range "Table"
norows = Table.Rows.Count
nocols = Table.Columns.Count

ReDim zarray(norows, nocols) As Single


Row = Table.Row - 1
Col = Table.Column - 1

For i = 1 To norows
For j = 1 To nocols
zarray(i, j) = Sheets(SN).Cells(i + Row, j + Col)
Next j
Next i


Is there any way I can isolate the worksheet name from the Range
parameter so that I can drop the SN parameter?


Best regards

RPH


--
RPH