Introduction to VBA in Excel - PDF Tutorial

Visual Basic for Applications, Excel's powerful built-in programming language, permits you to easily incorporate user-written functions into a spreadsheet.1

You can easily calculate Black-Scholes and binomial option prices, for example. Lest you think VBA is something esoteric which you will never otherwise need to know, VBA is now the core macro language for all Microsoft's office products, including Word. It has also been incorporated into software from other vendors. You need not write complicated programs using VBA in order for it to be useful to you. At the very least, knowing VBA will make it easier for you to analyze relatively complex problems for yourself.

This document presumes that you have a basic knowledge of Excel, including the use of built-in functions and named ranges. I do not presume that you know anything about writing macros or programming.
The examples here are mostly related to option pricing, but the principles apply generally to any situation where you use Excel as a tool for numerical analysis.

All of the examples here are contained in the Excel workbook VBA.XLS.

Table of contents

  • Introduction
  • Calculations without VBA
  • How to Learn VBA
  • Calculations with VBA
  • Creating a simple function
  • A Simple Example of a Subroutine
  • Creating a Button to Invoke a Subroutine
  • Functions can call functions
  • Illegal Function Names
  • Differences Between Functions and Subroutines

Overview of Excel VBA

EXCEL VBA (Visual Basic for Application) is a programming language to use Visual Basic code to run the many features of the EXCEL Application.

A program written in VBA is often called a macro.
Macros can be used to automate repetitive tasks under EXCEL. They can also be used to create dialog boxes to make an application developed under EXCEL more user-friendly.

A macro can be created using the macro recorder, which does not requires no knowledge of the VBA language.
However, a macro created in this way will only run on a set of cells data and product code will not always be very effective. To be able to create macros specific to its needs, effective and interactive, you have to learn how to program in VBA.

Create a Macro

Create a macro MACRO1 which writes in cell A1 the text "AgroParisTech" in bold type and in cell G1 the date of the day in italics.
Before you start recording, you need to ask yourself several questions:

  • Think about how to perform manual operations;
  • Ask when to start recording. In our case, the selection of cell A1 is part of the record since we want the text "AgroParisTech" is in this cell. If the record does not start with the selection of cell A1, the typed text is written to the active cell.
  • Ask when to stop recording.

Question: what to do so that the cell active after the execution of the macro is cell A1?

  1. Open a new workbook and save it as TEST-MACRO.XLS.
  2. The cells in a macro are identified by a letter (column) followed by a digit (line), such as cell A1. To make it easier to read the generated VBA code, it is best to choose the same identification of the cells in the workbook in which the macro is saved. If this is not the case, activate the TOOLS OPTIONS command, click on the GENERAL tab and uncheck the L1C1 REFERENCE STYLE box.
  3. Position the cursor on a cell other than cell A1 on the sheet FEUIL1.
  4. Activate the command MACRO TOOLS NEW MACRO.
  5. Type MACRO1 in the MACRO NAME box.
Size : 260.632 Kb
File type : pdf
Downloads: 1053
Created: 2018-05-23

Others VBA Tutorials

Excel VBA tutorial for professionals

VBA Notes for Professionals

VBA Tutorials for professionals

Introduction to Excel VBA Macro programming

Learning access-vba PDF course

Others related eBooks about Introduction to VBA in Excel - PDF Tutorial

Microsoft Excel 2013 for beginner

Download free Microsoft Office Excel 2013 Tutorial course material, tutorial training, PDF file on 25 pages....

Excel Basics: Microsoft Office 2013

This course is intended to beginning computer users. You are only expected to know how to use the mouse and keyboard, open a program, and turn the computer on and off. You should also be familiar with the Microsoft Windows operating system....

Evaluating Information Retrieval and Access Tasks

Download free course Evaluating Information Retrieval and Access Tasks, pdf file on 225 pages by Tetsuya Sakai, Douglas W. Oard, Noriko Kando....

Excel 2016 PivotTables and PivotCharts

Learn how to use PivotTable and PivotCharts in Microsoft Excel 2016, free training document for download....

Formulas & Functions in Microsoft Excel

Download Formulas and Functions in Excel PDF tutorial, free training document by Theresa Scott....

Beginning Excel 2019

Download free course Beginning Excel 2019, pdf file on 387 pages by by Noreen Brown, Barbara Lave, Hallie Puncochar, Julie Romey, Mary Schatz, Art Schneider, Diane Shingledecker....

PowerPoint 2013 course

Download Microsoft powerpoint 2013 courses for student, free training document in PDF designated for brginners....

VBA API Tutorial

Download VBA API Tutorial on 24 pages intended to advanced level users. Free training document in PDF....

Guide Excel 2010

A little guide in PDF,it will clarify the new basics features added to Microsoft Excel 2010 ,free training tutorial under 24 pages....

Publisher 2016 tutorial pdf

Download free course about Publisher 2016, getting started with Microsoft Office Publisher 2016 by Bharathi G, PDF training document under 182 pages....