VISUAL BASIC FOR APPLICATION (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:

A) 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.

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

We sorted the various section according to topics: Basics, User Interface, Functions and Statements, Managing your Sheets and some additional Tips.

BASICS: to get you rolling

1) Basics : what can you do with it? Here you will find basic examples of VBA, how to record a macro

2) Switch from one sheet to another with button

3) Jump to another area of the same sheet

4) 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.

5) 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.

6) Tips: Removing Duplicates in a list can be tedious, here we explain how to extract all the duplicates and delete the duplicate with VBA

7) Arrays and Variable in VBA. VBA is quite flexible and does not always requires to define what the variable is. But it is better to do it.

 

USER INTERFACE: everything to handle the user interface with forms and buttons

1) 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.

2) 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.

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

4) The Scrollbar or slider 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.

 

FUNCTIONS & STATEMENTS

1) Conditional Statement: 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.

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

3) Loop: The WHILE END statement in VBA will repeat some action as long as a certain condition is True.

4) Jump or Branch: GoTo statement  lets you branch or jump to another part of the program. Combined to the IF THEN statement, GOTO can be used as a loop statement.

5) Conditional Statement: Select Case (coming soon): let you select among a list of items and then do an action

6) Loop: DO WHILE, LOOP, EXIT DO statement is similar to WHILE statement but give you more branching possibilities. (coming soon)

7) Loop the entire range of a column or row in Excel VBA

 

MANAGING YOUR SHEETS

1) Add Worksheets with VBA, Count Worksheets and workbooks

 

BOOK REFERENCES

More in depth : good VBA book references.