VBA Excel Function to Set the Calling Cell .NumberFormat Property

Question

I created this function and it works to put the value into the cell but it doesnt work to set the .NumberFormat property.

Public Function NewYears(year As Integer)
    'Determine the cell that called the function
    Dim rng As range
    Set rng = ThisWorkbook.Sheets("How-To").range(Application.Caller.Address)
    MsgBox rng.Address
    fxFormat = "[$" & holidayName & "]"
    NewYears = DateSerial(year, 1, 1)
    rng.NumberFormat = fxFormat  
End Function

Update For more information:

I will be having functions like =NewYears() that returns a date.

I will do this for any number of holidays. I would like to format the field where it still stores the date but the .NumberFormat property has the name of the holiday

So =NewYears() would return "01/01/2014" but in the sheet it would appear as "New Years"

Kairan that's definitely not the info about sheet design/structure. Usually people do know how to call `UDF`... ;-) — bonCodigo, Sep 14 '13 at 02:19

score 0 · Answer 1 · answered Sep 13 '13 at 14:09

0

Functions can only return values or manipulate Comments, they can't modify formats directly.

answered Sep 13 '13 at 14:09

Gary's Student

95,722
10
59
99

Can I say, call another Sub that takes a range and the Sub can manipulate it? Of course I will give it a try – Kairan Sep 13 '13 at 14:29
You can call a sub to format the cells, but not from within the UDF. – Gary's Student Sep 13 '13 at 14:32
Use a sub to format the cells. Run the sub directly rather than being called from within the function. – Gary's Student Sep 13 '13 at 14:40
See [this answer](http://stackoverflow.com/a/9493006/445425) for a rather ugly work around to this UDF limitation – chris neilsen Sep 13 '13 at 16:42

David Zemens · Accepted Answer · 2013-09-13T15:47:19.320

0

Use the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Interior.ColorIndex = 39
    Target.NumberFormat = "mm/dd/yyyy" 
End Sub

Put this code in the Sheets("How-To") code module. Modify to whatever color/etc that you want to format.

When you initially enter the function in the cell, it will trigger the change event and this subroutine will execute.

Per Gary's comments (below), recalculation of existing formula will not trigger this event.

edited Sep 13 '13 at 15:47

answered Sep 13 '13 at 14:43

David Zemens

53,033
11
81
130

1

I don't believe the Work_Change will be triggered by a function calculation, you need a Calculate event macro to do that. – Gary's Student Sep 13 '13 at 15:33
ahhh I think you're right. I only tested it by *entering* the function in the cell, which of course triggers the change. – David Zemens Sep 13 '13 at 15:44
In this case, entering the function will initially set the number format. Not sure if that would ever need to change based on OP, but very good comment from you @Gary'sStudent. – David Zemens Sep 13 '13 at 15:46

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

How many calculations are in your sheet/book?
Are the cells in a particular arranged column or everywhere or Random?

If answer is Yes to first question I wouldn't suggest a volatile function triggers, well no one should. And things you do next depends on the answer to 2nd question.

Why dont you try "conditional formatting" though it could be a bit costly. Else if the Year cell should be in an organized column or cell make sure its format is ready-made to date.... if none of these apply, you may give us a better picture if your sheet's structure/design...

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 13 '13 at 14:57

bonCodigo

14,268
1
48
91

I added additional information to my original post - i think conditional would be difficult as my holiday dates may frequently change (for reasons I cant quickly explain) – Kairan Sep 13 '13 at 16:25

VBA Excel Function to Set the Calling Cell .NumberFormat Property

3 Answers3