0

How can i change formatting of the cell that uses vba function from the code of that function?

example i tried:

  1. made vba module (see code below)
  2. put in excel sheet in some cell "=test()"
  3. function "works" - it changes cell value and shows 2 popup windows. but formatting stays the same
Function test()
    MsgBox (Application.ThisCell.NumberFormat)        ' shows "General"
    Application.ThisCell.NumberFormat = "Currency"
    'Application.ThisCell.NumberFormat = "#,##0_);[Red](#,##0)"
    MsgBox (Application.ThisCell.NumberFormat)        ' still shows "General"

    test = 12345.6
End Function

How to make it work?

(i need custom formatting rule, not "currency", but custom rule (test example in commented line) doesn't work too)

braX
  • 10,905
  • 5
  • 18
  • 32
alt0172
  • 1
  • A User-Defined Function in a cell *can't* change the sheet formatting, except in some edge cases. It's not meant to be used to try changing the format, but just to return the value to the cell. – BigBen May 05 '20 at 20:26
  • 1
    https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel – Tim Williams May 05 '20 at 20:31
  • 1
    Seems you're just hard-coding a format not based on anything. Just set the formatting another way. You *can* write something to do that. Write a Sub that you call from a button on the worksheet or...somewhere else. – SmileyFtW May 05 '20 at 20:58
  • Workaround - https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280 – Tim Williams May 06 '20 at 00:50

0 Answers0