Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range in macro call
Is there a way to pass a named range (or even an unnamed block of cells) into
a macro? I have written a macro that I want to use for several different ranges of data. I have created multiple buttons that each call the macro, but I cannot seem to find the right syntax to pass in the range of data that I want the macro to use for each button. All I can do is to pass in the row and column numbers that define the upper left and lower right corners of the data range, as follows: Private Sub CommandButton1_Click() ' Call Macro1(UpperLeftRow, UpperLeftCol, LowerRightRow, LowerRightCol) Call Macro1(16, 9, 40, 35) End Sub Private Sub CommandButton2_Click() Call Macro1(51, 9, 75, 35) End Sub etc. If possible, I would like to be able to specify named ranges instead, so that I don't have to edit the code for all the buttons if I add, delete or move rows or columns. Is there a way to do this? Thanks! hjc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range in macro call
try something like this...
public sub macro1(rng as range) msgbox rng.address end sub Sub test Call macro1(thisworkbook.names("MyRange").referstorange ) end sub -- HTH... Jim Thomlinson "hjc" wrote: Is there a way to pass a named range (or even an unnamed block of cells) into a macro? I have written a macro that I want to use for several different ranges of data. I have created multiple buttons that each call the macro, but I cannot seem to find the right syntax to pass in the range of data that I want the macro to use for each button. All I can do is to pass in the row and column numbers that define the upper left and lower right corners of the data range, as follows: Private Sub CommandButton1_Click() ' Call Macro1(UpperLeftRow, UpperLeftCol, LowerRightRow, LowerRightCol) Call Macro1(16, 9, 40, 35) End Sub Private Sub CommandButton2_Click() Call Macro1(51, 9, 75, 35) End Sub etc. If possible, I would like to be able to specify named ranges instead, so that I don't have to edit the code for all the buttons if I add, delete or move rows or columns. Is there a way to do this? Thanks! hjc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range in macro call
Sub Macro1(myRng as range)
msgbox myrng.address(external:=true) End Sub And you can call it like: Dim Rng as range set rng = workbooks("Some workbook.xls").worksheets("sheet99").range("a1:b9" ) Call macro1(myRng:=rng) hjc wrote: Is there a way to pass a named range (or even an unnamed block of cells) into a macro? I have written a macro that I want to use for several different ranges of data. I have created multiple buttons that each call the macro, but I cannot seem to find the right syntax to pass in the range of data that I want the macro to use for each button. All I can do is to pass in the row and column numbers that define the upper left and lower right corners of the data range, as follows: Private Sub CommandButton1_Click() ' Call Macro1(UpperLeftRow, UpperLeftCol, LowerRightRow, LowerRightCol) Call Macro1(16, 9, 40, 35) End Sub Private Sub CommandButton2_Click() Call Macro1(51, 9, 75, 35) End Sub etc. If possible, I would like to be able to specify named ranges instead, so that I don't have to edit the code for all the buttons if I add, delete or move rows or columns. Is there a way to do this? Thanks! hjc -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range in macro call
This might also help along with what Jim Thomlinson offered.
This code snippet can be used to come up with the row and column numbers for the upper left corner and lower right corner of a named range, just substitute the actual name of the range where I show "NamedRangeName" in the snippet Dim UpperLeftRow As Long Dim UpperLeftCol As Long Dim LowerRightRow As Long Dim LowerRightCol As Long Dim rangeAddress As String rangeAddress = ThisWorkbook.Names("NamedRangeName").RefersToR1C1 rangeAddress = Right(rangeAddress, Len(rangeAddress) - _ InStr(rangeAddress, "!")) UpperLeftRow = Val(Mid(rangeAddress, 2, _ InStr(rangeAddress, "C") - 2)) UpperLeftCol = Val(Mid(rangeAddress, _ InStr(rangeAddress, "C") + 1, InStr(rangeAddress, ":") - _ InStr(rangeAddress, "C") + 1)) rangeAddress = Right(rangeAddress, Len(rangeAddress) - _ InStr(rangeAddress, ":")) LowerRightRow = Val(Mid(rangeAddress, 2, _ InStr(rangeAddress, "C") - 2)) LowerRightCol = Val(Right(rangeAddress, _ Len(rangeAddress) - InStr(rangeAddress, "C"))) 'then you could call your macro as: Call Macro1(UpperLeftRow, UpperLeftCol, LowerRightRow, LowerRightCol) "hjc" wrote: Is there a way to pass a named range (or even an unnamed block of cells) into a macro? I have written a macro that I want to use for several different ranges of data. I have created multiple buttons that each call the macro, but I cannot seem to find the right syntax to pass in the range of data that I want the macro to use for each button. All I can do is to pass in the row and column numbers that define the upper left and lower right corners of the data range, as follows: Private Sub CommandButton1_Click() ' Call Macro1(UpperLeftRow, UpperLeftCol, LowerRightRow, LowerRightCol) Call Macro1(16, 9, 40, 35) End Sub Private Sub CommandButton2_Click() Call Macro1(51, 9, 75, 35) End Sub etc. If possible, I would like to be able to specify named ranges instead, so that I don't have to edit the code for all the buttons if I add, delete or move rows or columns. Is there a way to do this? Thanks! hjc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
named range in macro call | Excel Programming | |||
Named Range Macro | Excel Discussion (Misc queries) | |||
Trying to call a macro on selection of a cell in a range | Excel Programming | |||
Call Macro when Cell within Range Changes | Excel Programming | |||
Passing a range in a macro call | Excel Programming |