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… image
  • New Project dialogue is displayed, select Excel 2010 Workbook and enter First Excel Project into Name and then click OK button.

image

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

image

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

image

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

image

image

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

image

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

imageimage

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.

image

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

image

Run the program on VBA.

imageimage

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.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

6 Responses to VBA vs. VS – Part 5: First Excel Project

  1. Pingback: VBA vs. VS – Part 1: Performance Review « Technologies and your business

  2. Pingback: VBA vs. VS – Part 2: Programming Paradigm Review « Technologies and your business

  3. Pingback: VBA vs. VS – Part 3: What is new in VB.Net? « Technologies and your business

  4. Pingback: VBA vs. VS – Part 4: .Net Framework « Technologies and your business

  5. Pingback: Happy 17 Birthday Visual Basic for Applications! « Technologies and your business

  6. Pingback: VBA vs. VS – Final: To Migrate or NOT To Migrate « Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: