Converting a VBA Macro to C# 4.0

Converting a VBA Macro to C# 4.0

Rate This
  • Comments 25

I've talked a lot about improved COM interop in C# 4.0 and how much easier it is now to work with Office applications. This time I want to share some tips and tricks on how you can convert Visual Basic for Applications (VBA) macros to C# 4.0 by using Office 2010 and Visual Studio 2010.

You can either watch a video or read this post: it’s the same scenario and the same code, only I tried a different medium this time. If you for some reason decide to take a look at both, let me know which one you liked more and why.

One common scenario for people working on Office applications is to record a macro in Office and then use the results in their code. It's often much faster and easier than looking up all the methods and properties.

To try this out, record a macro in Excel 2010: create a new workbook, fill a column with numbers from 1 to 10 by using the Auto Fill feature, change the color of the column, and then create a simple graph.

image

Here's the VBA macro you'll get as a result:

Sub Macro1()

    ActiveCell.FormulaR1C1 = "1"

    Range("A2").Select

    ActiveCell.FormulaR1C1 = "2"

    Range("A1:A2").Select

    Selection.AutoFill Destination:=Range("A1:A10"), Type:=xlFillDefault

    Range("A1:A10").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorAccent1

        .TintAndShade = 0.399945066682943

        .PatternTintAndShade = 0

    End With

    Range("A1:A10").Select

    ActiveSheet.Shapes.AddChart.Select

    ActiveChart.ChartType = xlConeColStacked

    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$A$10")

End Sub

Now open Visual Studio 2010, create a new project, and add this reference to the project: Microsoft.Office.Interop.Excel. Then copy the VBA script inside the following code:

using Excel = Microsoft.Office.Interop.Excel;

class Program
{
    static void Main(string[] args)
    {
        var excelApp = new Excel.Application();
        excelApp.Workbooks.Add();

        // Insert VBA code here.

        excelApp.Visible = true;
    }
}

Of course, the VBA code is all highlighted as an error. Here are the steps that you can use to convert that code into C# code:

  1. In this macro, all the objects are in fact properties of the Excel application object. Basically, you need to add excelApp to the beginning of each line.
    Press ALT and select an area at the start of the lines, as shown here:  
    image 
    Now type excelApp. and notice that it appears at the start of all the lines at once.
  2. You can use the same trick to add Selection.Interior to all the lines in the With block. Then delete the first and the last elements of the block (With and End With).

    image

  3. For all the Range objects, you need to replace the parentheses with square brackets. This is because in C# you need to use indexed properties, and their syntax requires, well, square brackets. In this piece of code all of the parentheses should become square brackets, so you can use the Find and Replace feature. (The keyboard shortcut for this is Ctrl + H.)
    image

  4. The next step is to replace the syntax for the named parameters. VBA uses the ":=" operator, while in C# 4.0 it's simply ":". Once again, simple find and replace can help.

  5. Now you need to add semicolons to each line, and add parentheses after each method call. You could probably find a converter that would do this for you, but here you have such a small piece of code that you can do it manually.

    Update:
    You can use the multi-line editing for adding semicolons as well. You can select a column in a so-called “virtual space”, where no characters were typed in.
    image
    So, you can type several semicolons at once and then reformat the document. Thanks to Jan B and David Nelson for the tip.

  6. Let’s take a look at the code once again.
     image

  7. The last thing to fix is the constants. Press Ctrl+Alt+J to open the Object Browser, and then search for each constant. You should find a class that contains this enumeration:
    image 
    All you need to do is to add the name of the class to the beginning of the constant.

    excelApp.Selection.AutoFill(
        Destination: excelApp.Range["A1:A10"],
        Type: Excel.XlAutoFillType.xlFillDefault);

That’s it. Now you can compile and run the program and get exactly the same picture that you saw at the beginning of this post. Here's the final version of this little program.

using Excel = Microsoft.Office.Interop.Excel;

class Program
{
    static void Main(string[] args)
    {
        var excelApp = new Excel.Application();
        excelApp.Workbooks.Add();

        excelApp.ActiveCell.FormulaR1C1 = "1";
        excelApp.Range["A2"].Select();
        excelApp.ActiveCell.FormulaR1C1 = "2";
        excelApp.Range["A1:A2"].Select();
        excelApp.Selection.AutoFill(
            Destination: excelApp.Range["A1:A10"],
            Type: Excel.XlAutoFillType.xlFillDefault);
        excelApp.Range["A1:A10"].Select();

        excelApp.Selection.Interior.Pattern = Excel.Constants.xlSolid;
        excelApp.Selection.Interior.PatternColorIndex = Excel.Constants.xlAutomatic;
        excelApp.Selection.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorAccent1;
        excelApp.Selection.Interior.TintAndShade = 0.399945066682943;
        excelApp.Selection.Interior.PatternTintAndShade = 0;

        excelApp.Range["A1:A10"].Select();
        excelApp.ActiveSheet.Shapes.AddChart.Select();
        excelApp.ActiveChart.ChartType = Excel.XlChartType.xlConeColStacked;
        excelApp.ActiveChart.SetSourceData(Source: excelApp.Range["Sheet1!$A$1:$A$10"]);

        excelApp.Visible = true;
    }
}

 

If you've read all that and still feel like you missed some steps, watch the video: How Do I: Convert Visual Basic for Applications Macro to C# 4.0

P.S.

Thanks to Mads Torgersen and Alex Turner for reviewing this and providing helpful comments, to Mick Alberts for editing.

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post
  • Re: Adding semicolons at the end of each line. Alt-select works for this, too.

  • @ Jan B

    I might be missing something, but as far as I know, block selection works in straight lines only. Since line endings do not form a straight line (do not belong to the same column), the block selection feature would be of a little help there.

  • Does this also work with VS 2008 as well?  I'll make some time to try it out later.

  • @PP

    No, it doesn't work in VS 2008. Indexed properties, named and optional parameters, etc. were all introduced in C# 4.0. The  multi-line editing is also VS 2010 feature.

  • Muito bom esse tutorial de como converter o VBA macro para C#.

  • @ Gustavo

    It took me a while to understand what language is that. But now I can say "Obrigado" :-)

  • Re: Adding semi-colons at the end of each line:

    Block selection works in virtual space, so you can create a block selection beyond the end of the longest line, add the semi-colon, then format the document to move all of the semi-colons to the end of their respective lines.

  • @David

    Yes, your are right. Thanks.

    I'll add it to the post. Unfortunately, it is much harder to change the video.

  • Hi Alexandra,

    Properly releasing COM Handles in C# 3.5 and below is a tricky business. As you know, the difficulty derives from the fact that .NET is calling the Excel object model through the COM Interop.

    var range =_excelApp.Workbooks[1].Cells[1,5] would leak three COM handles (the workbook collection handle, the Workbook handle and the Cells collection handle).

    It was necessary in C#3.5 to create a local variable for each handles and release them EXPLICITLY with

    Marshal.FinalReleaseComObject(). As you can imagine, it is really really frustrating

    Is it still necessary in .NET 4? As the management of Interop assembly objects been improved? Otherwise, your code, as neat as it is, will leak.

  • @Michael,

    I do not necessarily agree with you that you have to call Marshal.ReleaseComObject on EVERY COM interop handler. It is one possible technique.which is extremely frustrating.

    The other easier way is just to place a call to. GC.Collect(). GC.WaitForPendingFinalizers() which will collect the unrooted references. The advantage here is that you can strategically place such calls at the end of large operations with COM objects and this will cause all such temporary unrooted handles to be released. Usually, for small programs like above it is done before closing the workbook/applicaiton itself and usually this technique works well.

    Usually there is always an argument whether calling GC.Collect is a good programming technique and some people are shying away from using it... IMHO though this is a tool that is available which one can use (but know not to use it too often).

    .NET 4.0 does have some improvements in the general area of reference counting for COM objects but not something that would help in the above sample. The improvements has to do with managing references to COM objects that are passed to event handlers as event arguments (if you are curious - this is the relevant post blogs.msdn.com/.../better-eventing-support-in-clr-4-0-using-nopia-support.aspx) and you would only get these improvements if you use "Embed Interop Types" option when referencing Interop assemblies (fwiw, in the referenced post, codename NoPIA == "Embed Interop Types")

    Generally marrying mark-and-sweep garbage collectors (.NET) with reference counting systems is an extremely hard problem and the status of this problem has been quite well described by Brian Harry about 10 years ago www.socalnetug.org/.../determ_final.htm

    Hope this helps,

    Misha

  • Thanks, Misha!

  • i'm a beginner of C#,so this is helpfull for me  

  • i'm a beginner of C#,so this is helpfull for me  

  • i am facing some problem in converting the following block (VBA CODE) into  c#.

    Please Help!

    With Selection.find

         .Text = "^p"

         .Replacement.Text = ""

         .Forward = True

         .Wrap = wdFindContinue

         .Format = False

         .MatchCase = False

        .MatchWholeWord = False

        .MatchWildcards = False

        .MatchSoundsLike = False

        .MatchAllWordForms = False

    End With

    R`s

    VARDHMAN JAIN

    Broadridge

  • Better to use dymamic object in c# instead of refereing the excel library. And use activator to create the Excel.application class

Page 1 of 2 (25 items) 12