VBA function to define name in a worksheet

[Clinton W]

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

 

[Niek Otten]

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.

 

[Per Jessen]

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