Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format Numbers (97)

    Hello Everyone

    I am trying to format a number calculation to 4 decimal places but, I'm hitting a brick wall could someone please help me with this line of code?

    rUsdEur = Format(rUsd / rEur, "##,##0.0000")

    What I am trying to do is as follows:

    I have two lines of code which the user enters, firstly a Euro exchange rate and secondly a USDollar exchange rate. These values are held in variables rEur and rUsd respectively.

    My third variable calculates a cross rate and stores it's value in rUsdEur. Its this variable that I wish to limit to four decimal places.

    I hope my explanation is satisfactory.

    Many thanks
    Hayden

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format Numbers (97)

    Your statement converts your NUMBER to TEXT that is formatted.

    You can round it in VB with
    <pre>rUsdEur = application.worksheetfunction.round(rUsd / rEur, 4)</pre>

    if desired
    Then if/when you put the value into a cell, format that cell:
    <pre>Range("a1").value = rUsdEur
    range("a1").NumberFormat = "##,##0.0000"</pre>


    If you do NOT round it, excel will use 15 digits for calcs (it doesn't matter how many are displayed, xl ALWAYS uses 15 digits). The number format does NOT change the value, the rounding MIGHT.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Numbers (97)

    Thanks for the help with this Steve, I used the first line you suggested and it is working beautifully

    I would like to ask you one more thing regarding the values I have stored in the variables, if I may?

    I need to refer back to these values at different stages in different procedures, but each time I try and use the variable holding the rate I get an error saying that the variable is not defined. I'm sure there is a very simple answer to this but I cannot get it to work.

    Are you able to help me with this?

    Kind regards
    Hayden

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Numbers (97)

    There are different ways of declaring variables.

    Normally, a variable declared inside a sub or function is local to that sub or function:

    Sub Test1()
    Dim sTest as string
    sTest="sub1"
    Msgbox sTest
    Test2
    End Sub
    Sub Test2()
    Dim sTest as string
    Msgbox sTest
    End Sub

    If you run Test1, you first get a message that says "sub1" and then a message box that is empty, because sTest in Test2 is local to that sub and is not assigned a value.

    If you want to have a variable that is "visible" to all subs and functions in a module, you declare it at the top of that module:

    Dim sTest as string

    Sub Test1()
    sTest="sub1"
    Msgbox sTest
    Test2
    End Sub

    Sub Test2()
    Msgbox sTest
    End Sub

    Now running Test1 yields the same message twice: "sub1".

    But sTest is now available only to the subs and functions in that particular module.

    One can also declare a variable so that it is publicly available (to all modules in a workbook (project), by using the Public keyword:

    Public sTest as String

    And another way is to pass variables as arguments:

    Sub Test1()
    Dim sTest as string
    sTest="sub1"
    Msgbox sTest
    Test2 sTest
    End Sub

    Sub Test2(ByVal sString as String)
    Msgbox sString
    End Sub

    Clear as mud?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Numbers (97)

    Ah yes Jan as clear as mud indeed, thanks for this very useful piece of information, I now understand,
    well almost ........that last bit is a bit confusing but I'll get it eventually, not to worry

    Thanks for the help

    Regards
    Hayden

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Numbers (97)

    If I can come back to the issue of declaring variables for a second time.

    I have tried to declare a variable as Public so that I can use it in another module, but when I run the code the value of my variable is not being picked up in the new module.

    I have attached extracts from the code I am using to try and do this:

    In Module6 I declare the variables I want to use (highlighted in blue), I also ask the user to assign a value to the variables.
    In Module7 I am trying to use the value of the variables enter from Module6, but the value is not carried forward.

    What am I doing wrong?

    Kind regards
    Hayden

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Numbers (97)

    Hello everyone

    Please don't worry about this anymore, I have just re-run the code and it seems to work now.

    Sorry for the trouble

    Regards
    Hayden

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format Numbers (97)

    You must run StockSoldReConfigure once before calling other procedures that expect rEur etc. to have a value. If you have never run StockSoldReConfigure, then rEur etc. will be empty.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Numbers (97)

    To make sure my variables are initialized I use a public varaible called bVarsOK:

    Public bVarsOK as boolean

    Then in every relevant sub I have:

    If Not bVarsOK then InitMyProject

    Where InitMyProject is the initialisation sub, which contains

    bVarsOK=True at its end.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •