Using a worksheet name from a range

[RPH]

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

 

[Bernie Deitrick]

RPH,

Note that you can simply use

Table(i,j)

in place of your

zarray(i, j)

Ranges are treated as arrays, and your code (all of which is unnecessary, by the way) could have
been written as below.

Function Test(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

For i = 1 To norows
For j = 1 To nocols
zarray(i, j) = Table(i, j)
Next j
Next i
.....

But the end result is that you can just use Table(i,j) instead of your zarray(i,j).

HTH,
Bernie
MS Excel MVP

 

[JMB]

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).

 

[JMB]

FYI - my previous response assumes you may have some other reasons for
wanting to read that range into an array. As Bernie stated, Table(i,j) is
most likely your best solution. I think I read somewhere the only advantage
to reading data into an array first is if you have a large table and need to
access a number of different elements it is faster to use the array variable
than to constantly access the data on the worksheet.