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

 

Excel Visual Basic FAQ

This list of question is the same as the list in our page 1000 questions. It was important to have it here for the people looking for information on VBA only.

I frequently use a Macro - is there a way to quickly access the Macro in the Excel Ribbon?

I frequently use a Macro - is there a way to quickly access the Macro using a Keyboard Shortcut?

I want to automatically run an initialization procedure whenever I open my WorkBook so that it is ready for use - how can I achieve that?

How do I do a macro that disable the right mouse click option for users in my WorkBook?

Run a procedure or Macro at the closing of the workbook. Here the link

I have a WorkBook that loads a form automatically when it is opened. In Excel, how can I suppress the form from loading on file open when required?

How can I execute an event at a predetermined time from my Macro?

How can I make my macro wait for 5 secs before executing the next command?

I have a very complex macro that runs for a long time - is there any way I can set up an audible alarm indicating macro run is over?

How can I hide Formula Bar and Headings using VBA?

How can I hide all comments in my WorkSheet using VBA?

How to disable ability to insert Rows and Columns in Excel (using VBA)?

How can I prevent users from seeing / accessing my macro code?

How can I get users to select a file for processing using my macro?

How can I get users to select a folder to save the output of my macro?

How can I find the last used cell in a Column in VBA?

How can I extract file name from a full path including folder path and file name?

How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

How can I convert Column numbers into Column names for use in range definition?

How can I set the fill color, font color and set number format of cell to date?

How can I get input from user through a prompt and assign user's input to a cell?

How can I add a Timestamp after macro execution?

How can I sort data using VBA?

How can I loop through all WorkSheets and get their names using VBA?

How can I find the number of working days between 2 dates using VBA?

In VBA, how do I do a macro that copies a Table from one location to another and retain all formulas, formats and columnwidths?

How do I use Find to determine last occurrence of a string in a WorkSheet range using VBA?

How do I update my DropDown list whenever the sheet is activated?

How can I dynamically add a hyperlink using VBA?

How can I update a listbox based on data in a list using VBA?

How do I enter a formula in a cell using VBA (using Relative Reference)?

How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

How can I copy and rename a WorkSheet using VBA?

How can I add a WorkSheet and name it as required using VBA?

How do I create a macro that enters a formula in a cell (using Absolute Reference)?

How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

How can I filter and copy only filtered data using VBA?

In VBA, how can make a macro to count the rows of filtered data?

How can I run a vba macro when there is a change in value of a cell?

How can I clear cell after activating a macro when there is a change in value of a cell?

How can I set Page orientation, Zoom % , Title Rows and footer using VBA?

How can I set non-contiguous print area using VBA?

How can I set the Source Data of charts using VBA?

How can I dynamically add series to an existing chart using VBA?

How can I add a legend to a chart using VBA?

How can I get the count of number of series in a Chart using VBA?

How can I add Trendline to a chart using VBA?

How can I change the Marker size and Marker line color for all the series in a chart?

How can I create a macro that checks if a file exists in a folder using VBA?

How can I make a macro that lists all files in a folder using VBA?

How can I save a WorkSheet as a new WorkBook using VBA?

How can I export a WorkSheet as a PDF using VBA?

How do I add a shape using VBA?

How do I assign a macro to a shape using VBA?

How can I delete all shapes in a WorkSheet?

How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

How can I export a chart as a gif file?

How do I restrict user entry in ActiveX Text Box to just numeric values?

How can I set up ListBox using VBA to allow users to select multiple values?

How can I remove display of Gridlines in my worksheet using VBA?

I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

How can I turn off Alerts using VBA?

How can I avoid updates to cell values during macro execution?

How can I protect / unprotect WorkSheet using VBA?

How can I set FreezePanes in a certain range using VBA?

How can worksheet functions be accessed in VBA?

 

BOOK REFERENCES

More in depth : good VBA book references.