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