VISUAL BASIC (VBA) in Excel

WHAT is VBA? This is the programming language for all Microsoft applications. WORD, POWERPOINT, EXCEL, ACCESS. With VBA you can create programs that run in Excel or automate tasks that are repetitive or interact with the user of your Excel Spreadsheet.

I am just going to use part of the definition of Wikipedia for Visual Basic: Visual Basic is a third-generation event-driven programming language and integrated development environment (IDE) from Microsoft for its COM programming model first released in 1991. Microsoft intended Visual Basic to be relatively easy to learn and use. Visual Basic was derived from BASIC and enables the rapid application development of graphical user interface applications, access to databases using Data Access Objects, Remote Data Objects, or ActiveX Data Objects, and creation of ActiveX controls and objects.

Here the main items to remember are:

1) It is EVENT DRIVEN: meaning that an action (event) will trigger a reaction that you programmed. Typically a button press or a cell value change for example can trigger a reaction or a behaviour you have decided to program.

2) It is based on the BASIC language, so it is EASY to learn!! Really Easy. So try.

 

We are going to discuss :

Basics : what can you do with it? Here you will find basic examples of VBA

Buttons have a lot of potential to help you move around the user of your sheet.

Switch from one sheet to another with button

Jump to another area of the same sheet

Scrolling down the sheet with button. This is helpful if you have a very long sheet and want to jump from one section to another quickly and precisely.

Display a Message Box with MsgBox that allows you to interact with your user by displaying a message or values you want to show him. You can also ask him a decision by pressing OK or Cancel.

Get input from the user with the input box. This is the basic interaction you have in Excel and can help you guide your user through the steps of your sheet.

More in depth : good VBA references.

Add Worksheets, Count Worksheets and workbooks

Excel Workbook hierarchy explains how to write to a cell in the VBA code.  It explains you also how the various Objects in Excel are structured.

The IF THEN statement is used in Excel VBA to execute a command if a particular condition is met or not. With it you can redirect your User to different part of the Excel spreadsheet or make different actions depending on the inputs in the sheet.

The FOR TO NEXT statement  in VBA lets you loop through a table or your sheet and do multiple operation.

The Scrollbar is very useful to give the user the possibility to set value in an interactive and graphical way. Here we explain how to use the scrollbar or slider to set a cells value.