Use of VBA MID function to convert numbers to text

  • Hi
    Sorry if this has been asked before but I am trying to use / adapt the VBA function ConvertCurrencyToEnglish from your site (

    I am having a problem understanding the following line from the main function

    Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)

    From my understanding of the MID function there is one argument missing as it should be MID( text, start_num, num_chars) but the above only has the text and start_num values inside the parentheses.

    I also do not understand what the "00" is for in the LEFT function, nor for that matter given the above is for finding the Cents why you are taking the LEFT of the decimal place.

    Any guidance on how this is working would be gratefully received.

  • Re: Convert Numbers to Words/Text - Explanation Needed

    Hi DohNuts,

    Welcome to OzGrid! The length variable is optionsal. See Object Explorer


    Optional; Variant (Long). Number of characters to replace. If omitted, all of string is used.

    As for the double zero, that's probably something to do with data types on conversion to preserve formatting.



  • Re: Convert Numbers to Words/Text - Explanation Needed

    Thanks Craig for the reply

    Strange that Excel and VBA use different requirements for the same function (Excel required the number of characters to return - VBA doesn't!)

    I understand the MID function now, but the LEFT function with the "00",2) still alludes me.

    Thanks again for your support.


  • Re: Use of VBA MID function to convert numbers to text

    Hello Dave,

    Welcome to Ozgrid.
    Please note the change to your thread title. In future threads please do not use words/phrases like "Explanation Needed" are these dilute the title and not helpful search terms.

    FYI . . .

    Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, your title use as search terms would return relevant results.

    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
    • The title should not contain reference to VBA error messages as these are too generic to define the specific problem
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different
    • The title should not contain special characters such as < > ~ ! @ # $ % ^ & * ( ) or math operators - these are not search friendly

    Thanks. :cool:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!