Results 1 to 7 of 7
  1. #1
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Function to Add Range to Name (Excel 2000)

    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:
    <code>
    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
    </code>

    Is this possible? Any ideas where I'm going wrong?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function to Add Range to Name (Excel 2000)

    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)

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Function to Add Range to Name (Excel 2000)

    Thanks Hans,

    In that case I'll stop <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Function to Add Range to Name (Excel 2000)

    <P ID="edit" class=small>(Edited by macropod on 07-Feb-07 12:34. bug fix - 'On Error Resume Next' relocated)</P>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
    [MS MVP - Word]

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function to Add Range to Name (Excel 2000)

    Great!

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Function to Add Range to Name (Excel 2000)

    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
    [MS MVP - Word]

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Function to Add Range to Name (Excel 2000)

    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!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •