none
Unable to set the NumberFormat property of the Range class RRS feed

  • Question

  • Hello,

     

    I am trying to set NumberFormat of an excel cell to a currency format before inserting the data.

     

    Cells(Row, Column).NumberFormat = GetCellCurrencyFormat(lngCurrency)

     

    Public Function GetCellCurrencyFormat(ByVal lngCurrency As Long) As String

     

    GetCellCurrencyFormat = udtCurrency(lngCurrency).strSymbol & " #,##0.00"

    ‘ WHERE strSymbol for various currencies is as follows:

    ‘ lngCurrency 1 = $

    ‘ lngCurrency 2 = USD  ‘ alternatively, I can use “US$”.

    ‘ lngCurrency 3 = €

     

    End Function

     

    I am getting the error message “Unable to set the NumberFormat property of the Range class” when lngCurrency is 2 (= USD or US$); lngCurrency = 1 ($) or 3 (€) work both fine.

     

    Now that the USD is getting stronger, what’s Excel’s problem, J?

     

    Thank you for your help.

    Tuesday, October 4, 2011 5:51 PM

Answers

All replies

  • For your number "USD" currency number format try "[$USD] #,##0.00"

    Peter Thornton

    Tuesday, October 4, 2011 6:05 PM
    Moderator
  • Thank you - that works.

    If Len(m_udtCurrency(lngCurrency).strSymbol) < 2 Then
        ' For one letter symbols (e.g., "$" for CAD)
        GetCellCurrencyFormat = m_udtCurrency(lngCurrency).strSymbol & " #,##0.00"
    Else: GetCellCurrencyFormat = "[" & m_udtCurrency(lngCurrency).strSymbol & "] #,##0.00"
    End If

    The question for me now is to how to determine the appropriate abbreviations for the various currencies like British Pound, Swiss Franc, etc. Is there any Microsoft / VBA list?

     

    Tuesday, October 4, 2011 6:56 PM
  • Not sure I follow, there must be 100s of listings for currency abbreviations out there, no doubt MS produce one but here are a couple from a quick search -

    http://en.wikipedia.org/wiki/ISO_4217
    http://www.ratesfx.com/resources/symbols.html

    Peter Thornton

    Tuesday, October 4, 2011 8:22 PM
    Moderator