Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Format a cell with a custom function (Excel 2000 SP-3)

    I wrote a function to convert dates from yyyymmdd format to a numeric date (20061128 => 39049) but in addition to creating the date number I want the cell formatting changed to mm/dd/yy. Is there any way to do this automatically from within the function?


    Function DateConv(DATE8N) As Date

    Dim TextDate As String
    TextDate = Mid(DATE8N, 5, 2) & "/" & Right(DATE8N, 2) & "/" & Left(DATE8N, 4)
    DateConv = CDate(TextDate)

    End Function

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

    Re: Format a cell with a custom function (Excel 2000 SP-3)

    No, a user-defined function can only return a value, it cannot modify a cell in any other way. Any code intented to do that will simply be ignored. You'll have to change the format manually, or write a macro to do so.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format a cell with a custom function (Excel 2000 SP-3)

    Hi David
    Try the attached workbook. In addition to what you can see there is a hidden sheet which holds a named range. As well, there is code in the workbook.
    You should be able to set up the appropriate named range, and also copy the various procedures to your workbook.
    Regards
    Don

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Format a cell with a custom function (Excel 2000 SP-3)

    Interesting solution, but a bit of an overkill for how I would be using the formula. I am intrigued with your ThisWorkbook subs - I'm sure I'll find a use for them somewhere.

Posting Permissions

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