{"id":24933,"date":"2022-07-30T10:09:13","date_gmt":"2022-07-30T09:09:13","guid":{"rendered":"https:\/\/ekiwi-blog.de\/?p=24933"},"modified":"2023-02-25T20:16:48","modified_gmt":"2023-02-25T19:16:48","slug":"vba-excel-format-as-currency","status":"publish","type":"post","link":"https:\/\/ekiwi-blog.de\/en\/24933\/vba-excel-format-as-currency\/","title":{"rendered":"VBA Excel format as currency"},"content":{"rendered":"<p><abbr title=\"Visual Basic for Application\">VBA<\/abbr> has become a pleasant helper within my office routine to automate many things in the Microsoft Office world. Sometimes it happens that you want to format a cell in Excel as currency using VBA.<\/p>\n<p>In another article I had already shown how to <a title=\"VBA format as date!\" href=\"https:\/\/ekiwi-blog.de\/en\/18466\/vba-excel-als-datum-formatieren\/\">format an Excel cell as a date using VBA<\/a>. Now we will deal with currencies, although this procedure is also generally applicable for other formatting.<\/p>\n<p>In the example shown, we simply take an Excel cell in which a number is entered. As you can see, this cell has no formatting or is formatted as &#8220;<em>General<\/em>&#8220;.<\/p>\n<figure id=\"attachment_24930\" aria-describedby=\"caption-attachment-24930\" style=\"width: 600px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-24930 size-full\" title=\"format an excel cell to currency using VBA\" src=\"http:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/07\/vba-excel-format-as-currency.jpg\" alt=\"Screenshot Excel Cell formated as &quot;General&quot;\" width=\"600\" height=\"430\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/07\/vba-excel-format-as-currency.jpg 600w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/07\/vba-excel-format-as-currency-300x215.jpg 300w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/07\/vba-excel-format-as-currency-350x250.jpg 350w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/07\/vba-excel-format-as-currency-420x300.jpg 420w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><figcaption id=\"caption-attachment-24930\" class=\"wp-caption-text\">This cell should be formatted as currency via VBA<\/figcaption><\/figure>\n<p>Now there are two approaches how to do the currency formatting in VBA. This is somewhat dependent on what the rest of the VBA program looks like and what you plan to do with it.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of content<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/ekiwi-blog.de\/en\/24933\/vba-excel-format-as-currency\/#Use_Currency_data_type\" >Use Currency data type<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ekiwi-blog.de\/en\/24933\/vba-excel-format-as-currency\/#Use_NumberFormat_property\" >Use NumberFormat property<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/ekiwi-blog.de\/en\/24933\/vba-excel-format-as-currency\/#Format_cell_as_Euro_E\" >Format cell as Euro (\u20ac)<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/ekiwi-blog.de\/en\/24933\/vba-excel-format-as-currency\/#Format_cell_as_dollar\" >Format cell as dollar ($)<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/ekiwi-blog.de\/en\/24933\/vba-excel-format-as-currency\/#Use_FormatCurrency_function\" >Use FormatCurrency function<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Use_Currency_data_type\"><\/span>Use <em>Currency<\/em> data type<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Let&#8217;s assume that the value to be entered into the cell exists in the <a title=\"VBA macro for automatic attachment to email\" href=\"https:\/\/ekiwi-blog.de\/en\/49256\/vba-macro-automatically-add-a-file-attachment-to-an-e-mail-message\/\">VBA macro<\/a>. In this case we can easily define a <em>Currency<\/em> data type and assign the value to it. Then we pass the currency value to the cell in question.<\/p>\n<pre><code><span style=\"color: #0000ff;\">Sub<\/span> FormatAsCurrency_1()\r\n    Dim myCur <span style=\"color: #0000ff;\">As Currency<\/span>\r\n    myCur = 1.5678\r\n    Cells(2, 2).Value = myCur\r\n<span style=\"color: #0000ff;\">End Sub<\/span><\/code><\/pre>\n<p>Excel now automatically recognizes the data type and applies the correct formatting. Excel automatically takes into account the country-specific settings. So if your Office\/Windows is set e.g. to &#8220;<em>Germany<\/em>&#8221; or another european country, Excel will automatically format this to e.g. <strong>1.57 \u20ac<\/strong>.<\/p>\n<figure id=\"attachment_24932\" aria-describedby=\"caption-attachment-24932\" style=\"width: 600px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-24932 size-full\" title=\"VBA format cell as currency\" src=\"http:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/07\/vba-cell-format-as-currency.jpg\" alt=\"Screenshot Excel: the result of currency formatting\" width=\"600\" height=\"407\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/07\/vba-cell-format-as-currency.jpg 600w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/07\/vba-cell-format-as-currency-300x204.jpg 300w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><figcaption id=\"caption-attachment-24932\" class=\"wp-caption-text\">Result: A currency ($) formatted cell with <a href=\"https:\/\/ekiwi-blog.de\/en\/50316\/vba-excel-macro-save-spreadsheet-as-csv-file\/\" title=\"VBA Excel Macro: Save spreadsheet as CSV file\">VBA macro<\/a><\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"Use_NumberFormat_property\"><\/span>Use <em>NumberFormat<\/em> property<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Another case can be when, for example, the values are already present in the Excel spreadsheet. So it makes no sense to introduce the data type <em>Currency<\/em>. Then you can assign the data format directly to the respective cell. To do this, use the <strong>NumberFormat<\/strong> property.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Format_cell_as_Euro_E\"><\/span>Format cell as Euro (\u20ac)<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>For our example, it looks like this. We assign a formatting string to the <em>NumberFormat<\/em> property.<\/p>\n<pre><code><span style=\"color: #0000ff;\">Sub<\/span> FormatAsCurrency_2()\r\n    Cells(2, 2).NumberFormat = \"#,##0.00 \u20ac\"\r\n<span style=\"color: #0000ff;\">End Sub<\/span><\/code><\/pre>\n<p>In this example, the cell in question is then formatted as EUR in the format <strong>\u20ac1.57<\/strong>. In <a title=\"Vmware Workstation access to the local network with DHCP\" href=\"https:\/\/ekiwi-blog.de\/en\/18552\/vmware-workstation-access-to-the-local-network-with-dhcp\/\">the formatting string, do not get confused by the localizations<\/a>. In the VBA code you have to use the original formatting. That means as thousands separator the comma and as decimal separator the point. If you call <em>&#8220;Format Cells&#8221;<\/em> directly in Excel, it may be displayed the other way round, because Excel already takes the country-specific settings into account.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Format_cell_as_dollar\"><\/span>Format cell as dollar ($)<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>If you want to format the cell in a completely different currency, for example as a dollar in the format <strong>$1.57<\/strong> then the macro code would look like this:<\/p>\n<pre><code><span style=\"color: #0000ff;\">Sub<\/span> FormatAsCurrency_2()\r\n    Cells(2, 2).NumberFormat = \"$#,##0.00\"\r\n<span style=\"color: #0000ff;\">End Sub<\/span><\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Use_FormatCurrency_function\"><\/span>Use <em>FormatCurrency<\/em> function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In <a title=\"VBA function to open a form (UserForm) in Microsoft Office\" href=\"https:\/\/ekiwi-blog.de\/en\/17115\/vba-function-to-open-a-form-userform-in-microsoft-office\/\">VBA there is also an own function<\/a> called FormatCurrency, which supports the formatting of a value into a currency format. The disadvantage of this function is that it returns a string and thus the value in the Excel spreadsheet is formatted as text or string. Thus it is not suitable for further calculations in Excel.<\/p>\n<p>In this example, a value of data type <em>Double<\/em> is formatted as currency and assigned to the cell.<\/p>\n<pre><code><span style=\"color: #0000ff;\">Sub<\/span> FormatAsCurrency_3()\r\n    <span style=\"color: #0000ff;\">Dim<\/span> myVal <span style=\"color: #0000ff;\">As Double<\/span>\r\n    myVal = 1.5678\r\n    Cells(2, 2) = FormatCurrency(myVal, 2)\r\n<span style=\"color: #0000ff;\">End Sub<\/span><\/code><\/pre>\n<p>The <a href=\"https:\/\/docs.microsoft.com\/en-us\/office\/vba\/language\/reference\/user-interface-help\/formatcurrency-function\" target=\"_blank\" rel=\"noopener\">FormatCurrency<\/a> function provides several parameters for formatting here:<\/p>\n<p><strong>FormatCurrency<\/strong>(<em>Expression<\/em>, [ <em>NumDigitsAfterDecimal<\/em>, [ <em>IncludeLeadingDigit<\/em>, [ <em>UseParensForNegativeNumbers<\/em>, [ <em>GroupDigits<\/em> ]]]])<\/p>\n<p><strong>Expression:\u00a0<\/strong>This is the numerical value to be converted into a currency.<\/p>\n<p><strong>NumDigitsAfterDecimal:\u00a0<\/strong>This is an optional parameter. Here you can specify how many decimal places after the decimal point should be displayed.<\/p>\n\n<div class=\"aawp\">\n\n            \n            \n<div class=\"aawp-product aawp-product--horizontal aawp-product--bestseller aawp-product--ribbon\"  data-aawp-product-asin=\"1915476143\" data-aawp-product-id=\"110593\" data-aawp-tracking-id=\"ekiwide0b-21\" data-aawp-product-title=\"Mastering Excel VBA Programming  A Hands-On Guide to Automating Excel and Building Custom Solutions with VBA and Macros\">\n\n    <span class=\"aawp-product__ribbon aawp-product__ribbon--bestseller\">Bestseller No. 1<\/span>\n    <div class=\"aawp-product__thumb\">\n        <a class=\"aawp-product__image-link\"\n           href=\"https:\/\/www.amazon.de\/dp\/1915476143?tag=ekiwide0b-21&linkCode=ogi&th=1&psc=1&keywords=vba%20books\" title=\"Mastering Excel VBA Programming: A Hands-On Guide...\" rel=\"nofollow noopener sponsored\" target=\"_blank\">\n            <img decoding=\"async\" class=\"aawp-product__image\" src=\"https:\/\/m.media-amazon.com\/images\/I\/51i-tvtE+qL._SL160_.jpg\" alt=\"Mastering Excel VBA Programming: A Hands-On Guide...\"  \/>\n        <\/a>\n\n            <\/div>\n\n    <div class=\"aawp-product__content\">\n        <a class=\"aawp-product__title\" href=\"https:\/\/www.amazon.de\/dp\/1915476143?tag=ekiwide0b-21&linkCode=ogi&th=1&psc=1&keywords=vba%20books\" title=\"Mastering Excel VBA Programming: A Hands-On Guide...\" rel=\"nofollow noopener sponsored\" target=\"_blank\">\n            Mastering Excel VBA Programming: A Hands-On Guide...        <\/a>\n        <div class=\"aawp-product__description\">\n                    <\/div>\n    <\/div>\n\n    <div class=\"aawp-product__footer\">\n\n        <div class=\"aawp-product__pricing\">\n            \n                            <span class=\"aawp-product__price aawp-product__price--current\">31,02 EUR<\/span>\n            \n            <a href=\"https:\/\/www.amazon.de\/gp\/prime\/?tag=ekiwide0b-21\" title=\"Amazon Prime\" rel=\"nofollow noopener sponsored\" target=\"_blank\" class=\"aawp-check-prime\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ekiwi-blog.de\/wp-content\/plugins\/aawp\/assets\/img\/icon-check-prime.svg\" height=\"16\" width=\"55\" alt=\"Amazon Prime\" \/><\/a>        <\/div>\n\n                <a class=\"aawp-button aawp-button--buy aawp-button--icon aawp-button--icon-black\" href=\"https:\/\/www.amazon.de\/dp\/1915476143?tag=ekiwide0b-21&#038;linkCode=ogi&#038;th=1&#038;psc=1&#038;keywords=vba%20books\" title=\"Buy on Amazon\" target=\"_blank\" rel=\"nofollow noopener sponsored\">Buy on Amazon<\/a>\n            <\/div>\n\n<\/div>\n\n            \n            \n<div class=\"aawp-product aawp-product--horizontal aawp-product--bestseller aawp-product--ribbon\"  data-aawp-product-asin=\"3367105732\" data-aawp-product-id=\"112260\" data-aawp-tracking-id=\"ekiwide0b-21\" data-aawp-product-title=\"VBA mit Excel  Das umfassende Handbuch f\u00fcr Einsteiger und fortgeschrittene Anwender Aktuell zu Excel 2024 und Microsoft 365 \u2013 Ausgabe 2025\">\n\n    <span class=\"aawp-product__ribbon aawp-product__ribbon--bestseller\">Bestseller No. 2<\/span>\n    <div class=\"aawp-product__thumb\">\n        <a class=\"aawp-product__image-link\"\n           href=\"https:\/\/www.amazon.de\/dp\/3367105732?tag=ekiwide0b-21&linkCode=ogi&th=1&psc=1&keywords=vba%20books\" title=\"VBA mit Excel: Das umfassende Handbuch f\u00fcr...\" rel=\"nofollow noopener sponsored\" target=\"_blank\">\n            <img decoding=\"async\" class=\"aawp-product__image\" src=\"https:\/\/m.media-amazon.com\/images\/I\/51EVPKCMSSL._SL160_.jpg\" alt=\"VBA mit Excel: Das umfassende Handbuch f\u00fcr...\"  \/>\n        <\/a>\n\n            <\/div>\n\n    <div class=\"aawp-product__content\">\n        <a class=\"aawp-product__title\" href=\"https:\/\/www.amazon.de\/dp\/3367105732?tag=ekiwide0b-21&linkCode=ogi&th=1&psc=1&keywords=vba%20books\" title=\"VBA mit Excel: Das umfassende Handbuch f\u00fcr...\" rel=\"nofollow noopener sponsored\" target=\"_blank\">\n            VBA mit Excel: Das umfassende Handbuch f\u00fcr...        <\/a>\n        <div class=\"aawp-product__description\">\n                    <\/div>\n    <\/div>\n\n    <div class=\"aawp-product__footer\">\n\n        <div class=\"aawp-product__pricing\">\n            \n                            <span class=\"aawp-product__price aawp-product__price--current\">49,90 EUR<\/span>\n            \n            <a href=\"https:\/\/www.amazon.de\/gp\/prime\/?tag=ekiwide0b-21\" title=\"Amazon Prime\" rel=\"nofollow noopener sponsored\" target=\"_blank\" class=\"aawp-check-prime\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ekiwi-blog.de\/wp-content\/plugins\/aawp\/assets\/img\/icon-check-prime.svg\" height=\"16\" width=\"55\" alt=\"Amazon Prime\" \/><\/a>        <\/div>\n\n                <a class=\"aawp-button aawp-button--buy aawp-button--icon aawp-button--icon-black\" href=\"https:\/\/www.amazon.de\/dp\/3367105732?tag=ekiwide0b-21&#038;linkCode=ogi&#038;th=1&#038;psc=1&#038;keywords=vba%20books\" title=\"Buy on Amazon\" target=\"_blank\" rel=\"nofollow noopener sponsored\">Buy on Amazon<\/a>\n            <\/div>\n\n<\/div>\n\n    \n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>VBA has become a pleasant helper within my office routine to automate many things in the Microsoft Office world. Sometimes<\/p>\n","protected":false},"author":2,"featured_media":13630,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1555],"tags":[1702,1703,1704,2168,1859,1565,1705,1706],"class_list":["post-24933","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-software-en","tag-excel-en","tag-microsoft-en","tag-microsoft-office-en","tag-office-en","tag-programmieren-en","tag-software-en","tag-vba-en","tag-visual-basic-for-application-en"],"_links":{"self":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/24933","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/comments?post=24933"}],"version-history":[{"count":0,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/24933\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media\/13630"}],"wp:attachment":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media?parent=24933"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/categories?post=24933"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/tags?post=24933"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}