Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a worksheet name from a range
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a worksheet name from a range
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 "RPH" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a worksheet name from a range
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a worksheet name from a range
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. "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reduce the range of a saved worksheet? | Excel Discussion (Misc queries) | |||
subtotal a range of cells on a different worksheet | Excel Worksheet Functions | |||
Macro to print a selected range, not entire worksheet | Excel Discussion (Misc queries) | |||
can bin range for histograms be on a different worksheet? | Excel Discussion (Misc queries) | |||
limit worksheet view to specified cell range | Setting up and Configuration of Excel |