Apr 25 2013

Print this Post

Excel Visual Basic Scripts

A simple program like Microsoft Excel 2010 can be used as a powerful tool by manipulating the software. Microsoft has published several “how to” guides such as this one Getting Started with VBA in Excel 2010. They are very good articles, and I am not trying to beat them. After all, they wrote the software anyway. I just want to introduce Excel as a powerful programing tool for home users in baby steps. Let’s see if I can do it!

Note the colours for each type of code.

Note the colours for each type of code.

Before we do anything, let me introduce you to the helpful colour code or coding! All the comments must be started with the character “‘” and is coloured in green by the editor. The iterations and actions attached to a set is coloured in black (like regular texts). The red will only appear once you step into the dark side. I intentionally left “End Su” at the end to provide an example of an error. By highlighting it red send a message to the coder that something is wrong. You will also get a pop-up warning as you leave the troublesome line (unless you have disabled it).

Visual Basic for Applications

Some of you may have used a type of add-on to MS Office products known as Macros. Usually you either download a Macro from somewhere or someone gave it to you because you have some complicated data that needs to be processed. Macros are exactly that; they are flexible programing codes that can work with MS Excel to process data and generate useful outputs. For example, at the end of this article, I will show you how to write a Macro to send mass emails with user specific data. These Macros are written in a programming language known as Visual Basic (VB). It is the high-level programming language of Basic (Which I am not going to go into at this time). All Microsoft Excel products shipped with VB support including the Student Versions of the software.

In order to program in Macro, you need to enable the Developer Options in MS Excel:

  1. Right click on a ribbon (not on a an item)
  2. Select Customize the Ribbon
  3. Select Developer in the Excel Options window and click OK
  4. Ribbon Options

    Excel Options Window

You should now have the following Ribbon.

Developer Ribbon

Developer Ribbon

Security Concerns of VB

Like any other programming language, VB also has it’s own set of problems. Since all computers use code to operate and VB is a type of code, bad guys can use VB Macro scripts to steal your data and even harm your computer. Therefore Microsoft have disabled Macros by default. Before we do anything, we need to make sure that our security settings are desirable for VB programming. Let me assure you that unless you download an Excel file from an unknown source, for this tutorial you should not concern about the security (well, you are the one who writes the program anyway!). Go to the Developer Ribbon you just created and click on Macro Security under Code section.

Macro Security Settings

Macro Security Settings

I would recommend using Disable all macros with notifications option. This way you will protect yourself from unwanted attacks, but still allow you to program in VB.


VB Editor Window

VB Editor Window

Since almost all VB codes are similar to each other, I don’t see the value in writing a new code. So, I copied a code from Microsoft KB. However, I may improve the code in near future.

The following code will call in the Outlook (or your default mail program) to send an email with specific data from a cell. Once the script is in RUN, it will automatically execute the task. However, if you have a security setting to prevent this automation, a warning may pop up.

Sub Send_Range()
   ' Comments, in green, must start with "'".
   ' Select the range of cells on the active worksheet.
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
   End With
End Sub

Next time, advance cell based email generation. I am soooo excited!

Permanent link to this article: http://sanuja.com/blog/excel-visual-basic-scripts

2 pings

  1. Visual Basic Scripts using the Interface » Sanuja Senanayake

    […] This is the second article a on going series of VB scripting guide. If you are unfamiliar with programming in Microsoft Excel, don’t be afraid, start your journey into VisualBasic with basics of VB explained here. […]

  2. Extract data from rows for email » Sanuja Senanayake

    […] you have not work with VB Scripts before, please read the introduction article, here. I assume assume you know the fundamentals of Excel Macros. You may copy and paste the following VB […]

Comments have been disabled.