VBA vs. VS – Part 5: First Excel Project

So much about background and theory, let’s start our first Excel project using Visual Studio 2010.

After we started Visual Studio 2010, we can build our first Excel project:

• Select File, then New and choose Project…
• New Project dialogue is displayed, select Excel 2010 Workbook and enter First Excel Project into Name and then click OK button.

• We choose to Create a new document, click OK button

• Visual Studio creates the First Excel Project project, and adds the following files to the project.
• First Excel Project .xlsx – Represents the Excel workbook in the project. Contains all the worksheets and charts.
• Sheet1.vb file
• Sheet2.vb file
• Sheet3.vb file
• ThisWorkbook .vb file – Contains the design surface and the code for workbook-level customizations.

• In Solution Explorer, Right Click on Sheet1.vb and select View Code.

• Replace the Sheet1_Startup event handler with the following code. When Sheet1 is opened, this code adds Now to first 100,000 cells in Col A.

Private Sub Sheet1_Startup() Handles Me.Startup
    Cells(1, 1) = Now

    For i = 2 To 100000
        Cells(i, 1) = Now
    Next
End Sub

• When the code is ready, press F5 or click the green arrow button to build and run your project.

• Excel opened First Excel Project Workbook and it would take about 10 seconds to process and added Now to 100,000 cells.

Instead of cut and paste my code into the program, if you actually type my code then you would see VS editor is much smarter.  When I tried to type Cells in the editor, it would actually show me the variable name.

And when I finished the For statement, VS editor would automatically add the Next statement.

It is a very simple program, so I decided to repeat the simple code in an ordinary Excel 2010 VBA environment.

Run the program on VBA.

I am actually surprised to see that the same logic run faster in VS environment; I would expect the other way.  So I have to do more study to find out the reasons behind.  This is a very simple demo that you may never find in practical business environment.  So your real experience would definitely be not the same.  However, this demo should give you some idea how Excel (and other Office product) can be automated in VS as well.

There are 3 types of project templates for Excel, Add-in, Template and Workbook.  We were using Excel 2010 Workbook for this demo which is a document-level customizations.  This means the code is associated with a single workbook; we have to open this particular workbook to run the workbook.   If you want the code to be avialable for all Excel workbooks, then we should choose the Add-in template.  Add-ins is associated with  Microsoft Excel, regardless of which documents are open.

Conclusion

I am going to write 1 more article to wrap up my stories, I would discuss the situations that I would recommend my clients to migrate their application from VBA to VS.  VBA is a great development tool and I still use it to check results from the financial models  that I developed using C++.  However, I hope you can now understand that VBA has limits and there are tools on the market that can do a better development job under certain circumstances.  For example, do you want to integrate your line of business (LOB) systems like SAP or PeopleSoft with Microsoft Office suite?  It would be much easier to do it with VS than VBA, you can find some information from Microsoft MSDN: Microsoft Office and LOB integration.

• Happy 17 Birthday Visual Basic for Applications!

• VBA vs. VS – Part 1: Performance Review

• VBA vs. VS – Part 2: Programming Paradigm Review

• VBA vs. VS – Part 3: What is new in VB.Net?

• VBA vs. VS – Part 4: .Net Framework

• VBA vs. VS – Part 5: First Excel Project
  • VBA vs. VS – Final: To Migrate or NOT To Migrate

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!