Changing number formats through User Defined Functions (UDF)

[Glycko]

Hello,

I'm trying to create a function that converts currencies. When the currency is converted it also needs to change the number format of the cell that the function was typed in. This is so that it will reflect the new currency.

I have two snippets of code. It is the same code. One is in a sub routine and one is in a function. For some reason it will not let me change the number format (or cell format for that matter) with the function but works as expected with the sub routine. Is this a known VBA issue, or is there a workaround?


This code does nothing

Function numformat()
 
    Range("A2").NumberFormat = "£#,##0.00"
 
End Function

This code works exactly as expected

Sub numformat_sub()
 
    Range("A2").NumberFormat = "£#,##0.00"
 
End Sub

Cheers,

Andy

 

[VoG]

You cannot change cell formatting with a function.

 

[Glycko]

Hey VOG thanks for clarifying.

Would there be any fancy workaround? After the currency has been changed it would be difficult to see what the new currency was as it would just be a number.

Ultimately in the documentation I would have to tell the user to change the number format manually which isn't pretty but if there are no other ideas then this is what I plan on doing.

Once again many thanks,

Andy

 

[Glycko]

Hi guys,

For completeness I have since found a workaround and thought I would post it here. VoG once again thank you for your help in this problem.

The trick is to use the change event in the workbook. The funciton runs and performs a calculation as it should. When the calculation is finised a cell change event is fired off. In the cell change event it checks if the formula is the change currency formula and applies the formatting.

The code for this is as below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim currency_type As String
    
    '==============
    'If the formula in the current cell is the change_currency formula
    '==============
    If Left(Target.Formula, 12) = "=change_curr" Then
    
            '==============
            'Change currency formula syntax is
            '
            '=change_currency("from currency","to currency",cell ref)
            '
            'i.e. =change_current("GBP","USD",A1)
            '
            '==============
            
            'Obtain currency type by manipulating the text
            currency_type = Left(Replace(Target.Formula, "=change_curr(""", ""), InStr(1, Replace(Target.Formula, "=change_curr(""", ""), """", vbTextCompare) - 1)
            
            'Change the number format
            Select Case currency_type
            
                Case "GBP"
                    Target.NumberFormat = "£#,##0.00"
                Case "USD"
                    Target.NumberFormat = "[$$-409]#,##0.00"
            
            End Select
            
    End If
 
End Sub

Some more info on UDFs I should have read at the time:
http://www.ozgrid.com/VBA/Functions.htm#UDFIntro


Regards,

Andy