So you just want to write some code to format a cell in a worksheet. However, there isn’t a help topic in sight that shows you how to do it. There may be a topic out there, but you don’t have time to sift through the web searching for it. You’re not alone. The forums are filled with questions about how to accomplish what might seem like a simple task – like formatting a cell. But the search for the right class, method or property to do it is not so simple.
A great way to get unstuck on some of these small but distracting issues is to use the Excel macro recorder. Start the recorder, pick a cell, format the cell, stop the recorder and then look at the code that Excel generates. The trickiest part is translating the VBA code to C# or VB so that you can use the code in your VSTO project. However, doing this can be a lot easier than searching the web and MSDN for a topic that shows you which specific class or property in the massive Excel object model you need to perform your task.
Let’s take a look at an example scenario. All I want to do is format a cell as a percentage with 2 decimal places on the right side. The Macro recorder will show me which method or property I need to use.
To use the Macro recorder in Excel 2007, you have to enable the developer tab in the Ribbon. Click the round Office button and then click "Excel Options". Then select the "Show Developer tab in the Ribbon" as follows.
Start the macro recorder as follows:
Select a cell and then set your formatting options as follows:
Stop the macro recorder. Open the macro in the VBA editor as follows:
Grab the generated VBA. For example the VBA generated by the macro that I recorded looks like this:
Selection.NumberFormat = "0.00%"
Ok this is good. Basically the code is selecting cell B2 and then setting the "NumberFormat" property of the selected range to a percentage with 2 right-side decimal positions. I just need to open my VSTO project and add code that gets the range for cell B2, and then sets NumberFormat property of that range to "0.00%" as follows:
Excel.Range range1 = this.Range["B2", missing]; range1.NumberFormat = "0.00%";
Dim range1 As Excel.Range = Me.Range("B2") range1.NumberFormat = "0.00%"
This assumes that you add this code to a sheet class in a document-level customization project. If you are using an application-level add-in project, you might use something like the following which formats the cell "B2" in the default sheet of the workbook.
Excel.Range range1 = this.Application.get_Range("B2", missing); range1.NumberFormat = "0.00%";
Dim range1 As Excel.Range = Application.Range("B2") range1.NumberFormat = "0.00%"
I might have found a topic that shows me how to format a cell as a percentage, but sometimes using the macro recorder can be a lot faster. Especially if the task you want to perform is something that can be done by using common Excel features.
You can still search for answers in the docs. The topic Excel Object Model Overview is a great place to start. There is a section in that topic named "Using the Excel Object Model Documentation" that lists several resources.