Advantages of Using VBA in Excel 2007

VBA, or Visual Basic for Applications, is the simple programming language that can be used within Excel 2007 (and earlier versions, though there are a few changes that have been implemented with the Office 2007 release) to develop macros and complex programs. The advantages of which are:

– The ability to do what you normally do in Excel, but a thousand times faster

– The ease with which you can work with enormous sets of data

– To develop analysis and reporting programs downstream from large central databases such as Sybase, SQL Server, and accounting, financial and production programs such as Oracle, SAP, and others.

Macros save keystrokes by automating frequently used sequences of commands, and developers use macros to integrate Office with enterprise applications – for example, to extract customer data automatically from Outlook e-mails or to look up related information in CRM systems or to generate Excel spreadsheets from data extracted from enterprise resource planning (ERP) systems.

To create an Excel spreadsheet with functionality beyond the standard defaults, you write code. Microsoft Visual Basic is a programming environment that uses a computer language to do just that. Although VBA is a language of its own, it is in reality derived from the big Visual Basic computer language developed by Microsoft, which is now the core macro language for all Microsoft applications.

To take advantage of the functionality of the Microsoft Visual Basic environment, there are many suggestions you can use or should follow. Below we will take a look at a few hints and tips for VBA security and protection in Excel, a more in-depth understanding of which can be gained by attending a VBA Excel 2007 course, delivered by a Microsoft certified trainer.

Password protecting the code

As a VBA Excel user you may want to protect your code so that nobody may modify it and to protect against the loss of intellectual property if people access source code without permission. This is easily achieved in the VBE editor by going to “Tools/VBAProject Properties/Protection”. Check the box and enter a password.

Hiding worksheets

In any or all of your Excel workbooks you might want to hide a worksheet that contains sensitive or confidential information from the view of other users of the workbook. If you just hide the worksheet in the standard way the next user will be able to simply un-hide it, but by using a VBA method to hide and password protect a worksheet, without protecting the entire workbook, you will be able to allow other users access without affecting the confidentiality of the data.

Protecting workbooks

There are different levels of protection for workbooks, from not allowing anyone access to the workbook to not allowing any changes to be made to it, i.e. setting the security to ‘read only’ so that no changes can be made to the templates you have created.