how to conver vba code into c# code

8/22/2019 How to Conver VBA Code Into C# Code

1/14

Sign in

C# Frequently Asked Questions

The C# team posts answers to common questions and describes new language featuresSearch Blogs

Advanced search options...

Search In:

Date range:

Search

Search this blog Search all blogs

Options

Blog Home

Email Blog Author

Send to friend

RSS for Posts

Atom

RSS for Comments

Tags

.NET Framework

.NET Framework 3.5

.NET Framework 4

C#

C# 2.0

C# 3.0

C# 4.0

C# compiler

C#/VB.NET Equivalents

debugger

debugging

DLR

dynamic

dynamic language runtime

expression trees

IDE
https://login.live.com/login.srf?wa=wsignin1.0&rpsnv=11&ct=1291386193&rver=6.0.5286.0&wp=MBI&wreply=http:%2F%2Fblogs.msdn.com%2Fb%2Fcsharpfaq%2Farchive%2F2010%2F09%2F28%2Fconverting-a-vba-macro-to-c-4-0.aspx&lc=1033&id=271611http://blogs.msdn.com/b/csharpfaq/http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspxhttp://webform_dopostbackwithoptions%28new%20webform_postbackoptions%28%22ctl00%24content%24ctl00%24fragment_11224%24ctl01%24ctl01%24ctl01%24searchbutton%22%2C%20%22%22%2C%20true%2C%20%22%22%2C%20%22%22%2C%20false%2C%20true%29%29/http://blogs.msdn.com/b/csharpfaq/http://blogs.msdn.com/b/csharpfaq/contact.aspxmailto:?subject=Converting%20a%20VBA%20Macro%20to%20C%23%204.0&body=http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspxhttp://blogs.msdn.com/b/csharpfaq/rss.aspxhttp://blogs.msdn.com/b/csharpfaq/atom.aspxhttp://blogs.msdn.com/b/csharpfaq/rsscomments.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/tags/-NET+Framework/http://blogs.msdn.com/b/csharpfaq/archive/tags/-NET+Framework+3-5/http://blogs.msdn.com/b/csharpfaq/archive/tags/-NET+Framework+4/http://blogs.msdn.com/b/csharpfaq/archive/tags/-NET+Framework+4/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+2-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+3-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+4-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+compiler/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_23002F00_VB-NET+Equivalents/http://blogs.msdn.com/b/csharpfaq/archive/tags/debugger/http://blogs.msdn.com/b/csharpfaq/archive/tags/debugging/http://blogs.msdn.com/b/csharpfaq/archive/tags/DLR/http://blogs.msdn.com/b/csharpfaq/archive/tags/dynamic/http://blogs.msdn.com/b/csharpfaq/archive/tags/dynamic+language+runtime/http://blogs.msdn.com/b/csharpfaq/archive/tags/expression+trees/http://blogs.msdn.com/b/csharpfaq/archive/tags/IDE/http://blogs.msdn.com/b/csharpfaq/http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspxhttp://webform_dopostbackwithoptions%28new%20webform_postbackoptions%28%22ctl00%24content%24ctl00%24fragment_11224%24ctl01%24ctl01%24ctl01%24searchbutton%22%2C%20%22%22%2C%20true%2C%20%22%22%2C%20%22%22%2C%20false%2C%20true%29%29/http://blogs.msdn.com/b/csharpfaq/http://blogs.msdn.com/b/csharpfaq/contact.aspxmailto:?subject=Converting%20a%20VBA%20Macro%20to%20C%23%204.0&body=http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspxhttp://blogs.msdn.com/b/csharpfaq/rss.aspxhttp://blogs.msdn.com/b/csharpfaq/atom.aspxhttp://blogs.msdn.com/b/csharpfaq/rsscomments.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/tags/-NET+Framework/http://blogs.msdn.com/b/csharpfaq/archive/tags/-NET+Framework+3-5/http://blogs.msdn.com/b/csharpfaq/archive/tags/-NET+Framework+4/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+2-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+3-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+4-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+compiler/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_23002F00_VB-NET+Equivalents/http://blogs.msdn.com/b/csharpfaq/archive/tags/debugger/http://blogs.msdn.com/b/csharpfaq/archive/tags/debugging/http://blogs.msdn.com/b/csharpfaq/archive/tags/DLR/http://blogs.msdn.com/b/csharpfaq/archive/tags/dynamic/http://blogs.msdn.com/b/csharpfaq/archive/tags/dynamic+language+runtime/http://blogs.msdn.com/b/csharpfaq/archive/tags/expression+trees/http://blogs.msdn.com/b/csharpfaq/archive/tags/IDE/https://login.live.com/login.srf?wa=wsignin1.0&rpsnv=11&ct=1291386193&rver=6.0.5286.0&wp=MBI&wreply=http:%2F%2Fblogs.msdn.com%2Fb%2Fcsharpfaq%2Farchive%2F2010%2F09%2F28%2Fconverting-a-vba-macro-to-c-4-0.aspx&lc=1033&id=271611


2/14

parallel-processing

parallel-programming

reflection

Task Parallel Library

Tips

TPL

Visual Studio

Visual Studio 2010

WPF

ArchiveArchives

November 2010(1)

October 2010(1)

September 2010 (1)

August 2010(1)

July 2010(2)

June 2010 (2)

May 2010 (1)

April 2010(2)

March 2010 (1)

February 2010 (1)

January 2010(2)

November 2009(1) October 2009(2)

September 2009 (1)

March 2009 (1)

January 2009(1)

October 2006(2)

March 2006 (3)

February 2005 (1)

December 2004(4)

November 2004(1)

October 2004(15)

August 2004(3)

July 2004(3) June 2004 (1)

May 2004 (8)

April 2004(4)

March 2004 (36)

Converting a VBA Macro to C# 4.0
http://blogs.msdn.com/b/csharpfaq/archive/tags/parallel_2D00_processing/http://blogs.msdn.com/b/csharpfaq/archive/tags/parallel_2D00_programming/http://blogs.msdn.com/b/csharpfaq/archive/tags/reflection/http://blogs.msdn.com/b/csharpfaq/archive/tags/Task+Parallel+Library/http://blogs.msdn.com/b/csharpfaq/archive/tags/Tips/http://blogs.msdn.com/b/csharpfaq/archive/tags/TPL/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio+2010/http://blogs.msdn.com/b/csharpfaq/archive/tags/WPF/http://blogs.msdn.com/b/csharpfaq/archive/2010/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/09.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/08.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/08.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/07.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/07.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/06.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/05.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/04.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/04.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/03.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/02.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/01.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/01.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/09.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/03.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/01.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/01.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2006/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2006/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2006/03.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2005/02.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/12.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/12.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/08.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/08.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/07.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/07.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/06.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/05.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/04.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/04.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/03.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/tags/parallel_2D00_processing/http://blogs.msdn.com/b/csharpfaq/archive/tags/parallel_2D00_programming/http://blogs.msdn.com/b/csharpfaq/archive/tags/reflection/http://blogs.msdn.com/b/csharpfaq/archive/tags/Task+Parallel+Library/http://blogs.msdn.com/b/csharpfaq/archive/tags/Tips/http://blogs.msdn.com/b/csharpfaq/archive/tags/TPL/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio+2010/http://blogs.msdn.com/b/csharpfaq/archive/tags/WPF/http://blogs.msdn.com/b/csharpfaq/archive/2010/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/09.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/08.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/07.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/06.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/05.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/04.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/03.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/02.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/01.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/09.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/03.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2009/01.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2006/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2006/03.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2005/02.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/12.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/11.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/10.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/08.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/07.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/06.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/05.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/04.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2004/03.aspx


3/14

Converting a VBA Macro to C# 4.0

Alexandra Rusina27 Sep 2010 5:30 PM

Comments 17

I've talked a lot about improved COM interop in C# 4.0 and how much easier it is now to work withOffice applications. This time I want to share some tips and tricks on how you can convert Visual Basicfor 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: its the same scenario and the same code, only I tried adifferent medium this time. If you for some reason decide to take a look at both, let me know whichone you liked more and why.

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

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

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
http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx#commentshttp://msdn.microsoft.com/en-us/vcsharp/ff962528.aspxhttp://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-33-48-metablogapi/4670.image_5F00_68F53564.pnghttp://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx#commentshttp://msdn.microsoft.com/en-us/vcsharp/ff962528.aspx


4/14


With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0.399945066682943

.PatternTintAndShade = 0

End With


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;

classProgram

{

staticvoid 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 convertthat 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:


5/14

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).

3. For all the Range objects, you need to replace the parentheses with square brackets. Thisis 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
http://msdn.microsoft.com/en-us/library/ee310208.aspxhttp://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-33-48-metablogapi/4263.image_5F00_03FDDEA6.pnghttp://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-33-48-metablogapi/0535.image_5F00_0D3A19E7.pnghttp://msdn.microsoft.com/en-us/library/ee310208.aspx


6/14

you can use the Find and Replace feature. (The keyboard shortcut for this is Ctrl + H.)

4. The next step is to replace the syntax for thenamed 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 asmall 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 columnin a so-called virtual space, where no characters were typed in.

So, you can type several semicolons at once and then reformat the document. Thanks toJan B and David Nelson for the tip.
http://msdn.microsoft.com/en-us/library/dd264739.aspxhttp://msdn.microsoft.com/en-us/library/dd264739.aspxhttp://msdn.microsoft.com/en-us/library/dd264739.aspxhttp://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-33-48-metablogapi/4744.image_5F00_7C726F38.pnghttp://msdn.microsoft.com/en-us/library/dd264739.aspx


7/14

6. Lets take a look at the code once again.

7. The last thing to fix is the constants. Press Ctrl+Alt+J to open the Object Browser, andthen search for each constant. You should find a class that contains this enumeration:

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);

Thats it. Now you can compile and run the program and get exactly the same picture that you saw atthe beginning of this post. Here's the final version of this little program.
http://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-33-48-metablogapi/1033.image_5F00_581D64E9.pnghttp://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-33-48-metablogapi/1440.image_5F00_1BB5160C.png


8/14

using Excel = Microsoft.Office.Interop.Excel;

classProgram

{

staticvoid 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.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.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 VisualBasic for Applications Macro to C# 4.0

P.S.

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

17 Comments
http://msdn.microsoft.com/en-us/vcsharp/ff962528.aspxhttp://msdn.microsoft.com/en-us/vcsharp/ff962528.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx#commentshttp://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx#commentshttp://msdn.microsoft.com/en-us/vcsharp/ff962528.aspxhttp://msdn.microsoft.com/en-us/vcsharp/ff962528.aspxhttp://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx#comments


9/14

IDE, Tips,Visual Studio 2010,C# 4.0,C#,Visual Studio,VBA, Visual Basic for Applications

Comments

Jan B27 Sep 2010 6:59 PM

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. Sinceline endings do not form a straight line (do not belong to the same column), the block selection featurewould be of a little help there.

PP29 Sep 2010 10:14 PM

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 allintroduced in C# 4.0. The multi-line editing is also VS 2010 feature.
http://blogs.msdn.com/b/csharpfaq/archive/tags/IDE/http://blogs.msdn.com/b/csharpfaq/archive/tags/Tips/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio+2010/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio+2010/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+4-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+4-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio/http://blogs.msdn.com/b/csharpfaq/archive/tags/VBA/http://blogs.msdn.com/b/csharpfaq/archive/tags/VBA/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Basic+for+Applications/http://blogs.msdn.com/b/csharpfaq/rsscomments.aspxhttp://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://www.linkedin.com/shareArticle?mini=true&ro=true&url=http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx&title=Converting%20a%20VBA%20Macro%20to%20C#%204.0%20-%20C%23%20Frequently%20Asked%20Questions%20-%20Site%20Home%20-%20MSDN%20Blogs&summary=&source=Microsoft&armin=arminhttp://del.icio.us/post?url=http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx&title=Converting%20a%20VBA%20Macro%20to%20C#%204.0%20-%20C%23%20Frequently%20Asked%20Questions%20-%20Site%20Home%20-%20MSDN%20Blogshttp://digg.com/submit?url=http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx&title=Converting%20a%20VBA%20Macro%20to%20C#%204.0%20-%20C%23%20Frequently%20Asked%20Questions%20-%20Site%20Home%20-%20MSDN%20Blogshttp://twitter.com/home?status=Converting%20a%20VBA%20Macro%20to%20C#%204.0%20-%20C%23%20Frequently%20Asked%20Questions%20-%20Site%20Home%20-%20MSDN%20Blogs%20:%20http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspxhttp://www.facebook.com/sharer.php?u=http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspx&t=Converting%20a%20VBA%20Macro%20to%20C#%204.0%20-%20C%23%20Frequently%20Asked%20Questions%20-%20Site%20Home%20-%20MSDN%20Blogshttp://blogs.msdn.com/b/csharpfaq/archive/tags/IDE/http://blogs.msdn.com/b/csharpfaq/archive/tags/Tips/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio+2010/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_+4-0/http://blogs.msdn.com/b/csharpfaq/archive/tags/C_2300_/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Studio/http://blogs.msdn.com/b/csharpfaq/archive/tags/VBA/http://blogs.msdn.com/b/csharpfaq/archive/tags/Visual+Basic+for+Applications/http://blogs.msdn.com/b/csharpfaq/rsscomments.aspxhttp://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/


10/14

Gustavo Amanai1 Oct 2010 4:56 AM

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

Alexandra Rusina1 Oct 2010 10:13 AM

@ Gustavo

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

David Nelson4 Oct 2010 8:47 AM

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 thelongest line, add the semi-colon, then format the document to move all of the semi-colons to the endof their respective lines.

Alexandra Rusina4 Oct 2010 9:53 AM

@David

Yes, your are right. Thanks.

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

Michael Sevestre7 Oct 2010 6:10 AM

Hi Alexandra,

Properly releasing COM Handles in C# 3.5 and below is a tricky business. As you know, the difficultyderives from the fact that .NET is calling the Excel object model through the COM Interop.
http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/


11/14

var range =_excelApp.Workbooks[1].Cells[1,5] would leak three COM handles (the workbook collectionhandle, 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.

Misha Shneerson7 Oct 2010 11:15 PM

@Michael,

I do not necessarily agree with you that you have to call Marshal.ReleaseComObject on EVERY COMinterop 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 willcollect the unrooted references. The advantage here is that you can strategically place such calls atthe end of large operations with COM objects and this will cause all such temporary unrooted handlesto be released. Usually, for small programs like above it is done before closing theworkbook/applicaiton itself and usually this technique works well.

Usually there is always an argument whether calling GC.Collect is a good programming technique andsome people are shying away from using it... IMHO though this is a tool that is available which one canuse (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 butnot something that would help in the above sample. The improvements has to do with managingreferences to COM objects that are passed to event handlers as event arguments (if you are curious -this is the relevant postblogs.msdn.com/.../better-eventing-support-in-clr-4-0-using-nopia-support.aspx) and youwould only get these improvements if you use "Embed Interop Types" option when referencing Interopassemblies (fwiw, in the referenced post, codename NoPIA == "Embed Interop Types")

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

Hope this helps,

Misha

Alexandra Rusina8 Oct 2010 12:22 PM

Thanks, Misha!
http://blogs.msdn.com/members/Misha-Shneerson/http://blogs.msdn.com/members/Misha-Shneerson/http://blogs.msdn.com/b/mshneer/archive/2008/10/28/better-eventing-support-in-clr-4-0-using-nopia-support.aspxhttp://blogs.msdn.com/b/mshneer/archive/2008/10/28/better-eventing-support-in-clr-4-0-using-nopia-support.aspxhttp://www.socalnetug.org/reference/determ_final.htmhttp://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/Misha-Shneerson/http://blogs.msdn.com/members/Misha-Shneerson/http://blogs.msdn.com/b/mshneer/archive/2008/10/28/better-eventing-support-in-clr-4-0-using-nopia-support.aspxhttp://www.socalnetug.org/reference/determ_final.htmhttp://blogs.msdn.com/members/alrusina/


12/14

pam27 Oct 2010 2:43 AM

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

pam27 Oct 2010 2:43 AM

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

vardhman jain9 Nov 2010 8:31 PM

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


13/14

R`s

VARDHMAN JAIN

Broadridge

prabhash22 Nov 2010 4:26 AM

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

Alexandra Rusina22 Nov 2010 6:23 PM

@prabhash

First of all, using the new dynamic feature doesn't mean that you don't need libraries anymore. Youstill have to reference the Excel library all the same.

Second, the dynamic feature is often used implicitly in COM interop in C# 4.0 - the interfaces thatreturned "object" before now return "dynamic".

I am not sure about "activator". Why do you think it's better? MSDN examples usually use theApplication() method: msdn.microsoft.com/.../dd264733%28VS.100%29.aspx

Patrick23 Nov 2010 5:38 AM

whats the benefit of using C# to replace VBA? Can you distribute an Excel workbook with 'macros'written this way?

Leave a Comment

Name

Comment

Post
http://blogs.msdn.com/members/alrusina/http://msdn.microsoft.com/en-us/library/dd264733(VS.100).aspxhttp://webform_dopostbackwithoptions%28new%20webform_postbackoptions%28%22ctl00%24content%24ctl00%24fragment_11231%24ctl01%24ctl00%24ctl00%24ctl05%24bpcommentform%24ctl05%24btnsubmit%22%2C%20%22%22%2C%20true%2C%20%22blogpostcommentform-ctl00_content_ctl00_fragment_11231_ctl01_ctl00%22%2C%20%22%22%2C%20false%2C%20true%29%29/http://blogs.msdn.com/members/alrusina/http://blogs.msdn.com/members/alrusina/http://msdn.microsoft.com/en-us/library/dd264733(VS.100).aspxhttp://webform_dopostbackwithoptions%28new%20webform_postbackoptions%28%22ctl00%24content%24ctl00%24fragment_11231%24ctl01%24ctl00%24ctl00%24ctl05%24bpcommentform%24ctl05%24btnsubmit%22%2C%20%22%22%2C%20true%2C%20%22blogpostcommentform-ctl00_content_ctl00_fragment_11231_ctl01_ctl00%22%2C%20%22%22%2C%20false%2C%20true%29%29/


14/14

MSDN Blogs > C# Frequently Asked Questions> Converting a VBA Macro to C# 4.0

2010 Microsoft Corporation.

Terms of Use

Trademarks

Privacy Statement

Report Abuse
http://blogs.msdn.com/http://blogs.msdn.com/b/csharpfaq/http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspxhttp://msdn.microsoft.com/en-us/cc300389.aspxhttp://msdn.microsoft.com/en-us/cc300389.aspxhttp://www.microsoft.com/library/toolbar/3.0/trademarks/en-us.mspxhttp://www.microsoft.com/info/privacy.mspxhttp://webform_dopostbackwithoptions%28new%20webform_postbackoptions%28%22ctl00%24fragment_997b9e2f_f981_4067_aea5_8b1db81bf69f%24ctl01%24ctl00%24ctl01%24lnkabuse%22%2C%20%22%22%2C%20true%2C%20%22%22%2C%20%22%22%2C%20false%2C%20true%29%29/http://blogs.msdn.com/http://blogs.msdn.com/b/csharpfaq/http://blogs.msdn.com/b/csharpfaq/archive/2010/09/28/converting-a-vba-macro-to-c-4-0.aspxhttp://msdn.microsoft.com/en-us/cc300389.aspxhttp://www.microsoft.com/library/toolbar/3.0/trademarks/en-us.mspxhttp://www.microsoft.com/info/privacy.mspxhttp://webform_dopostbackwithoptions%28new%20webform_postbackoptions%28%22ctl00%24fragment_997b9e2f_f981_4067_aea5_8b1db81bf69f%24ctl01%24ctl00%24ctl01%24lnkabuse%22%2C%20%22%22%2C%20true%2C%20%22%22%2C%20%22%22%2C%20false%2C%20true%29%29/

how to conver vba code into c# code

Documents