If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VBA function to define name in a worksheet
I'm looking for a way to use a VB function to define a named range and alter
the quantity of cells within the named range. I recorded a macro defining a named range from the "Insert" menu so I could get the VB code. Running a Sub using this code works fine, but I need to run it by calling a function. Below is what I've done. The Sub selectRange_SpareCopy achieves the result I want to get from the function, so I included it as an example. Function selectRange(rangeName, sheet_RowColumn) 'Called by the selectSheetRange Sub ActiveWorkbook.Names.Add Name:="rangeName", RefersToR1C1:= _ "=sheet_RowColumn" End Function Sub selectSheetRange() 'Give range (A1:A7) on Sheet 1 the name "RangeOne" Call selectRange("RangeOne", "Sheet1!R1C1:R7C1") End Sub Sub selectRange_SpareCopy() 'I've kept this here because this Sub works, and the result I get from this _ is the result I want to achieve with the selectRange function ActiveWorkbook.Names.Add Name:="RangeOne", RefersToR1C1:="=Sheet1!R1C1:R7C1" Range("A1").Select End Sub I imagine the main problem will be the lack of DIM as .... and Set rangeName As.... but despite all my experimenting I just don't know what to set these up as. Could anyone help me please? Thank you Regards, Clinton |
#2
|
|||
|
|||
VBA function to define name in a worksheet
Hi Clinton,
A VBA function, called from a worksheet (directly or indirectly) cannot change anything at all in Excel's worksheet environment. The only thing it is meant to do and allows you to is to return a value to replace the call to it. -- Kind regards, Niek Otten Microsoft MVP - Excel "Clinton W" wrote in message ... I'm looking for a way to use a VB function to define a named range and alter the quantity of cells within the named range. I recorded a macro defining a named range from the "Insert" menu so I could get the VB code. Running a Sub using this code works fine, but I need to run it by calling a function. Below is what I've done. The Sub selectRange_SpareCopy achieves the result I want to get from the function, so I included it as an example. Function selectRange(rangeName, sheet_RowColumn) 'Called by the selectSheetRange Sub ActiveWorkbook.Names.Add Name:="rangeName", RefersToR1C1:= _ "=sheet_RowColumn" End Function Sub selectSheetRange() 'Give range (A1:A7) on Sheet 1 the name "RangeOne" Call selectRange("RangeOne", "Sheet1!R1C1:R7C1") End Sub Sub selectRange_SpareCopy() 'I've kept this here because this Sub works, and the result I get from this _ is the result I want to achieve with the selectRange function ActiveWorkbook.Names.Add Name:="RangeOne", RefersToR1C1:="=Sheet1!R1C1:R7C1" Range("A1").Select End Sub I imagine the main problem will be the lack of DIM as .... and Set rangeName As.... but despite all my experimenting I just don't know what to set these up as. Could anyone help me please? Thank you Regards, Clinton |
#3
|
|||
|
|||
VBA function to define name in a worksheet
Hi Clinton
A function can not manipulate a sheet or a reference directly! Use a (private) sub which can be called from your main sub. Your variables has been declared, and notice that text in quotation signs are seen as plain text, so the quotation signs around the variables has been removed. Private Sub selectRange(rangeName As String, sheet_RowColumn As String) 'Called by the selectSheetRange Sub ActiveWorkbook.Names.Add Name:=rangeName, RefersToR1C1:= _ "=" & sheet_RowColumn End Sub Sub selectSheetRange() 'Give range (A1:A7) on Sheet 1 the name "RangeOne" Call selectRange("RangeOne", "Sheet1!R1C1:R7C1") End Sub Regards, Per "Clinton W" skrev i meddelelsen ... I'm looking for a way to use a VB function to define a named range and alter the quantity of cells within the named range. I recorded a macro defining a named range from the "Insert" menu so I could get the VB code. Running a Sub using this code works fine, but I need to run it by calling a function. Below is what I've done. The Sub selectRange_SpareCopy achieves the result I want to get from the function, so I included it as an example. Function selectRange(rangeName, sheet_RowColumn) 'Called by the selectSheetRange Sub ActiveWorkbook.Names.Add Name:="rangeName", RefersToR1C1:= _ "=sheet_RowColumn" End Function Sub selectSheetRange() 'Give range (A1:A7) on Sheet 1 the name "RangeOne" Call selectRange("RangeOne", "Sheet1!R1C1:R7C1") End Sub Sub selectRange_SpareCopy() 'I've kept this here because this Sub works, and the result I get from this _ is the result I want to achieve with the selectRange function ActiveWorkbook.Names.Add Name:="RangeOne", RefersToR1C1:="=Sheet1!R1C1:R7C1" Range("A1").Select End Sub I imagine the main problem will be the lack of DIM as .... and Set rangeName As.... but despite all my experimenting I just don't know what to set these up as. Could anyone help me please? Thank you Regards, Clinton |
Thread Tools | |
Display Modes | |
|
|