I Just Want to Format a Cell in My Excel Worksheet (Norm Estabrook)

  • Comments 3

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.

image image

Start the macro recorder as follows:

image

Select a cell and then set your formatting options as follows:

image

Stop the macro recorder.  Open the macro in the VBA editor as follows:

image image

Grab the generated VBA. For example the VBA generated by the macro that I recorded looks like this:

Range("B2").Select
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:

[C#]

Excel.Range range1 = this.Range["B2", missing];
range1.NumberFormat = "0.00%";

[VB]

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.

[C#]

Excel.Range range1 = this.Application.get_Range("B2", missing);
range1.NumberFormat = "0.00%";

[VB]

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.

Norm E.

Leave a Comment
  • Please add 6 and 1 and type the answer here:
  • Post
  • PingBack from http://exceltutoriales.com/?p=1373

  • I'm an Professional Excel Developer, I've been doing Excel for about 13 years, and VSTO for about 1 year.

    But if I take out my knowledge of both, and look at this example....

    Range("B2").Select

    Selection.NumberFormat = "0.00%"

    to

    Microsoft.Office.Tools.Excel.NamedRange myNamedRange =

        this.Controls.AddNamedRange(this.get_Range("B2", missing), "myNamedRange");

    myNamedRange.NumberFormat = "0.00%";

    I would cry....

    surely I'd want to encourage ppl to use vsto. I suggest the following (but I know there are a million ways to do anything)

    arng = ((Excel.Worksheet)this).Range("B2")

    arng.NumberFormat = "0.00%"

    or the simpler

    ((Excel.Worksheet)this).Range("B2").NumberFormat = "0.00%"

    And then offer that they use extension methods which are part of powertools.

  • Great feedback. Thank you.  

    I updated the example code to be a bit simpler. This example now uses native Excel range objects and not a VSTO NamedRange control.  

    For straight formatting, a native range object is all you need.

Page 1 of 1 (3 items)

I Just Want to Format a Cell in My Excel Worksheet (Norm Estabrook)