Re-calculates identification of currency macro through entire macro repeatedly up on calculation of any cell in the workbook

[patsdavixen]

Hi, I needed a macro that would identify the currency in a particular currency formated cell in my worksheet. I have included the code below. The problem is that since the code has "Application.Volatile" in it, everytime a cell has a formula or needs to be calculated, the following currency code runs again. I have more than 100 cells that need to be calculated in my macro and this slows it down considerably. Could some one please help me?

Private Function GetCurrency(ByVal r As Range) As String
     Application.Volatile
     Static RegX As Object
     If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
          With RegX
               .Global = True
               .Pattern = "[0-9\-\.,\s]"
               GetCurrency = .Replace(r.Text, "")
          End With
End Function

Thanks,
Pat

 

[Firefly2012]

Hi Pat

A simple way would be to have the particular currency of interest stated in an adjacent cell to the value (not based on a UDF as you have above).

 

[patsdavixen]

Hi Firefly2012,

The currency is stated in a particular cell along with the amount, this information is filled in by multiple users. I need to use this currency macro because I need to prepare a seperate report that picks out the currency only in one column and the amount in another. I am not sure if there is a better way of capturing the currency and amount seperately other than this UDF, but it was the only macro that worked perfectly. Is there a way to make it non-volatile or lock the macro so it runs only when it is called?
Any help would be appreciated.

 

[Firefly2012]

Well, you have it as a UDF at the moment, right? You could remove the Application.Volatile.

Alternatively, remove it as a formula from the sheet and have it written as a macro that runs when selected from a button press/selected from a menu?

I have to be honest, if the user already selects the CCY in a given cell, why not just reference this cell to determine the CCY?