Function to Add Range to Name (Excel 2000)

Topic

New Reply

Hi all,

I’ve got an Excel workbook in which I want to use a UDF to test whether the calling cell falls within a particular named range and, if not, add the cell’s address to the named range. I can do this quite OK using the Sub ‘CellFormat’ below, but the Function ‘Fmt’ fails to update the named range and no error message is generated:

Sub CellFormat()
If Intersect(Selection, ActiveSheet.Range("Fmt1")) Is Nothing Then _
ActiveWorkbook.Names.Add Name:=" Fmt1", _
RefersTo:=ActiveWorkbook.Names.Item("Fmt1") & "," & Selection.Address
End Sub

Function Fmt(ByVal Num As Long) As Long
If Intersect(Selection, ActiveSheet.Range("Fmt1")) Is Nothing Then _
ActiveWorkbook.Names.Add Name:=" Fmt1", _
RefersTo:=ActiveWorkbook.Names.Item("Fmt1") & "," & Selection.Address
Fmt = Num
End Function


Is this possible? Any ideas where I’m going wrong?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Replies

User-defined functions can only change the value displayed in the cell that contains the formula. They cannot change any other aspect of the workbook – attempts to do so are silently ignored.

(There are some exceptions, see for example Modifying Shapes (and Charts) With UDFs)

Reply | Quote

Thanks Hans,

In that case I’ll stop

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

(Edited by macropod on 07-Feb-07 12:34. bug fix – ‘On Error Resume Next’ relocated)

Hi Hans,

I’ve figured out a way around this. First some background:

What I’ve been trying to do is to apply ordinal number formatting to a cell, or a range of cells on a given worksheet, via a UDF. The following two sub-routines, placed in the relevant worksheet

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Great!

Reply | Quote

The theory’s great, but it’s not ready for prime time yet – as coded, any cell/range that is selected gets added to the ‘Ordinal’ name. A bit more work should get that sorted though.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Given that you are already using the worksheet events, why don’t you just use them to add the range to the named range if necessary? I guess what I’m saying is that I don’t get the point of the OrdNum function!

Reply | Quote