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