How to call Macro in UDF

[Anand the Smarty]

Dear Excel Genius guys

i have a small query
can there be Numberformat in UDF (User Defined Function)

My VBA Code is

Function Crore (Selcell as variant)
crore=selcell/10000000
end function

now i want that at the same time its number format should also changed
& show us
"#,#00.000[$ Cr.]" 2 Cr.

now for that i have macro

sub crnumberformat()
ActiveCell.NumberFormat = "#,#00.000[$ Cr.]"
end sub

my query is how to call a macro in function

 

[Nick Hodge]

Anand

You will need to re-think this for two reasons

1) You can only return a value from a function and this is not formatted
2) You would be calling a sub BEFORE any value was returned (even if it
would work)

You might look at doing your formatting using a Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents=False
Target.NumberFormat = "#,#00.000[$ Cr.]"
Application.EnableEvents=True
End Sub

You would also be able to restrict this, for example to column A by the
following

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Columns("A"), Target) Is Nothing Then
Application.EnableEvents = False
Target.NumberFormat = "#,#00.000[$ Cr.]"
Application.EnableEvents = True
End If
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk