Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts
    Please help me to verify these tentative observations, when debugging an app in MS Excel (2003) that has several UserForms:

    1. When retrieving a value from a {text box, combo box, list box}, the data type will be a string. Your code has to use data-conversion functions like CCur, CLng on that string.
    Example: varX = UserForm1.txtTotal.Value will return the string "$332.45". So, curX = CCur(varX) would be the next line, right?

    2. Regardless of what data conversion functions the developer used to calculate some data for a {text box, combo box, list box}, the loading of the data into the object will work without an error, because those objects accept the data as a string value.
    Example:
    varX = CCur(<some expression>)
    UserForm1.txtTotal.Value = varX <--- This will be loaded as a string? And the CCur function above is unnecessary?

    (This question is occasioned by my failure to understand how to Step-Through the code in this app, using either the Immediate Window or the Watch Window, to observe the data types. I've tried to apply "Application.EnableEvents = False", without success.)

    The original developer wrote dozens of lines of code to deal with currency values, going into and out of text boxes on the forms. He/she particularly used the FormatCurrency( ... ) and FormatNumber( ... ) functions to set up values before loading into text boxes. I have suspected that this code is unnecessary, right?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It's correct that the value of a text box on a userform is always of type text.

    VBA performs automatic conversion between data types, so you usually don't need CCur and other conversion functions. For example, the following will work:

    Dim curX As Currency
    curX = 23.45

    Me.txtTotal = curX

    which will just enter a number in txtTotal, or

    Me.txtTotal = Format(curX, "Currency")

    which will enter the number formatted as currency.

    The other way round works too:

    curX = Me.txtTotal

    Formatting the value in the text box can be useful, since text boxes themselves don't know how to format numbers, unlike cells in a worksheet.

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts
    Hans: ...That's good news. Thanks very much!
    jes

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There are some exceptions to not having to explicitly convert values to text: when the formatting matters to you. For example with date and time values.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='pieterse' post='768594' date='01-Apr-2009 09:47']There are some exceptions to not having to explicitly convert values to text: when the formatting matters to you. For example with date and time values.[/quote]
    That's true - if you don't use the Format function, dates and times will always be displayed using the system's Short Date and Time format.

Posting Permissions

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