Скачать или смотреть How to Add Comma to a Number in Text using Excel VBA

Learn how to format numbers with commas, making them bold, in Excel using VBA for dynamic reporting.
---
This video is based on the question https://stackoverflow.com/q/65733667/ asked by the user 'eren deliaslan' ( https://stackoverflow.com/u/10862353/ ) and on the answer https://stackoverflow.com/a/65733751/ provided by the user 'Pᴇʜ' ( https://stackoverflow.com/u/3219613/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to add comma to a number in the text in Excel VBA

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Formatting Numbers in Excel using VBA

When working with Excel, particularly while creating automated reports, it's common to format numeric values in a more visually appealing way. One specific requirement might be to insert commas into numbers and to make certain text bold. If you're engaged in such a project, this guide will guide you on how to add commas to numbers and format the text using Excel VBA.

The Challenge

Let's say you have data inputs in Sheet2, and you want to present them in a specific format in Sheet1. For example:

In Sheet2, you might have:

C10=1000

C11=150000

In Sheet1, you'd like to see:

C4= 1,000.00 MWh

C5= 150,000 Dollar

You encountered a problem: although your initial VBA code successfully retrieved the numbers and made them bold, it did not format the numbers with commas.

Solution Overview

Fortunately, there’s a simple solution to properly format your numbers in Excel using VBA. The key is to set the .NumberFormat property and ensure the values being displayed are numeric. Below are the steps to achieve the desired formatting.

Step-by-Step Solution

Retrieve Values: Start by fetching the values from the specified cells.

Apply Number Format: Use the .NumberFormat property to format these values with commas.

Display Values: Insert the formatted values into your target cells, along with the relevant units (e.g., "MWh" or "Dollar").

Make Text Bold: Ensure the numeric parts of the text are bold for clarity.

Implementing the Code

Here's how you can modify and implement your VBA code:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

Variable Declaration: Using Dim, Range1 and Range2 are declared to store the data from Sheet2.

Setting Cell Value: The values from C10 and C11 are assigned directly to C4 and C5.

Number Formatting:

For MWh, the format # ,# # 0.00 "MWh" adds commas and ensures two decimal points.

For Dollar, the format # ,# # 0 "Dollar" adds commas without decimal points.

Bold Text Formatting: The .Characters() method allows you to specify the number of characters to bold, which in this case is the entire numeric part.

Conclusion

By following this structured approach, you can seamlessly add comma formatting to numbers in Excel using VBA and enrich your reports with bold styling. This not only enhances the aesthetic appeal of your spreadsheets but also improves data readability. If you have any further questions or need additional tips, feel free to leave a comment below!

Now you can present clean, professional reports with ease.