Anthony's Excel VBA Page - Excel Tutorial - Excel Consultant

Anthony's Excel VBA (Macro) Tutorial

This page contains Excel and VBA (Macro) tutorial examples on various topics such as finance, mathematics, statistics and other general issues. Users can learn Excel VBA topics range from simple issues such as using VBA recorder to record macro, computing sum, average, median and standard deviation to advanced issues such as Black-Scholes and Binomial option pricing models, multiple regression, portfolio optimization, probability distribution random numbers generator, Bootstrap, Monte Carlo Simulation, risk analysis and many more.

Excel VBA examples and tutorials on this website are being placed into the following categories:

Excel VBA Basic Tutorial Series

Excel VBA step by step examples and tutorials for beginners. Shows users how to create VBA programs from scratch. Beginner can create his or her first VBA application in minutes. Three (3) series in this section.

Excel VBA Simulation Basic Tutorial Series

Introductory and immediate VBA examples and tutorials. Two (2) series in this section.

Excel VBA Statistics and Mathematics Examples

Introductory and immediate VBA examples and tutorials.

Excel VBA Projects

Intermediate and advanced Excel VBA examples on finance, mathematics, statistics and other general issues. Thirteen (13) projects (11 with open source code).

Excel on the Web Examples (New) (Archives)

Interactive Excel examples on the Web (no VBA). Mostly using formulas (functions).

Excel (No VBA) Statistics and Finance Examples

Excel examples without using VBA.

Visual Basic for Applications is very similar to the stand-alone Visual Basic programming language. It is the macro language for all of Microsoft's major applications. This home page provides examples in VBA for Excel. One of the features that makes VBA for Excel very powerful is its ability to use "Excel engine" in its programming language. For example, writing a program to invert a matrix can be a very complicated and troublesome. However, one can use the Excel build-in functions to perform such tasks by incorporating the functions into the program.

The objective of this website is to demonstrate how to apply Excel VBA in various academic fields such as economics, finance, statistics, and mathematics and provide step by step examples in programming the projects to help the readers gain a better understanding on how the projects are approached - though some of the coding might seem inefficient in term of programming. The contents of this website should be used for academic purpose only, even though the examples used can also be applying to the real world situation. All the examples can be accessed under the VBA section of this site.

Hope you enjoy this site. Please also visit this website's forum section where readers can post their questions and share their knowledge.

Anthony <><

P.S. There are tons of examples on this web site and new examples are being added on a regular base. Here are some of the example collections (many free source codes):

Excel Business Solutions Consultancy

Excel Business Solutions provides spreadsheet and database solutions through Excel automation and business modeling. Each of our consultants has more than 10 years of professional experiences with spreadsheet modeling in various Excel projects.

Keywords: Excel Consulting Service, Excel Consultancy, Excel Consultant , Excel Devoloper, Excel Programmer.

Excel VBA Basic Tutorial Series

Excel VBA Basic Tutorial Series 1

Creating Your First Macro
In this sub section, we will show you how to create your first macro (VBA program). We will use the world classic "Hello World!" example.

Recording a Marco
In this example, we will record a macro that sets the cell background color to light yellow.

See the Recorded Syntax

Before we run the marco, let's look into the syntax.

Run the Recorded Marco
Run the recorded macro in the worksheet.

Modules and Procedures and Their Scope
A module is a container for procedures as shown in our prior examples. A procedure is a unit of code enclosed either between the Sub and End Sub statement or between the Function and End Function statements.

Calling Sub Procedures and Function Procedures
There are two ways to call a sub procedure. This example shows how a sub procedure can be called by other sub procedures....

Passing Argument by Value or by Reference

If you pass an argument by reference when calling a procedure, the procedure access to the actual variable in memory. As a result, the variable's value can be changed by the procedure.

Excel VBA Basic Tutorial Series 2

Objects and Collections

Objects are the fundamental building blocks of Visual Basic. Anobject is a special type of variable that contains both data and codes. A collection is a group of objects of the same class. The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range.

Workbook and Worksheet Object

A workbook is the same as an Excel file. The Workbook collection contains all the workbooks that are currently opened. Inside of a workbook contains at least one worksheet.

Range Object and Cells Property

Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. We will show you some examples on how Range object can be used.

Methods and Property

Each object contains its own methods and properties. A Property represents a built-in or user-defined characteristic of the object. A method is an action that you perform with an object.

Assigning Object Variables and Using Named Argument

Sometime a method takes more than one argument. For example, the Open method for the Workbook object, takes 12 arguments.

Excel VBA Basic Tutorial Series 3

Objects and Collections

Objects are the fundamental building blocks of Visual Basic. An object is a special type of variable that contains both data and codes. A collection is a group of objects of the same class. The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range.

Workbook and Worksheet Object

A workbook is the same as an Excel file. The Workbook collection contains all the workbooks that are currently opened. Inside of a workbook contains at least one worksheet.

Range Object and Cells Property

Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. We will show you some examples on how Range object can be used.

Methods and Property

Each object contains its own methods and properties. A Property represents a built-in or user-defined characteristic of the object. A method is an action that you perform with an object.

Assigning Object Variables and Using Named Argument

Sometime a method takes more than one argument. For example, the Open method for the Workbook object, takes 12 arguments.

Excel VBA Simulation Basic Tutorial 101

Creating and Managing Array

This is an essential skill for creating a simulation. Topics in this section cover declare an array, resize an array, manage dynamic array, create multi-dimensional array, and find the array size.

Decision Structure - IF and Select Case

Contains tutorial using If...Else, If...ElseIf, and Select Case statements.

Loop Structure - For...Next and Loops

Provides examples on all the loop structures: For...Next with Step, Do...While...Loop, So Until...Loop, and etc.

Sorting Numbers in an Array

This tutorial shows how to call a sort sub procedure, passes the array to it, and returns a sorted array.

Finding Max and Min in Array

Extract the maximum and the minimum values in an array using sorting method.

Double Sorting - The Secret of Resampling Without Replacement

This example shows how to derive unique random values by sorting one array based on another array.

Simulation Basic Tutorial Series

Excel VBA Simulation Basic Tutorial 102

Random Number and Randomize

One of the most essential 'Must Know' in simulation.

Standard Deviation and Mean

Two of the mostly used statistic estimates in action.

Skewness and Kurtosis

Second tier statistic estimates.

Percentile and Confidence Interval

Compute the simulated values at a specific percentile.

Profitablity

Compute the probability at a specific value (or profit).

Creating a Histogram

A tutorial on how to create a histogram by passing array into the histogram sub procedure and returns the frequency distribution from the array.

Excel VBA Statistics and Math

Finding Median

Compute the median from an array

Generate Random Number From Uniform Distribution

Generate uniform random number within a range.

Sum Number in an Array

Sum up number in a array using loop.

Compute Factorial

Compute binomial coefficient.

Binomial Coefficient

Compute the probability at a specific value (or profit).

Cumulative Standard Normal Distribution

Compute the probabilty from a standard normal distribution.

Excel VBA Projects on Finance and Statistics

A simple tutorial that shows users on how to compute mean and standard deviation from an array (also a demonstration of creating user-defined functions).

This example involves generating multiple unique random numbers from 1 to 54 with resampling without replacement technique by utilizingsorting one array based on another array.

What is the probability of getting 3 cards with red hearts and two other cards when 5 cards are drawn from a deck? This tutorial simulates an actual scenario. The probability distribution derived from this simulation happens to be a Hypergeometric distribution.

This tutorial shows how to create random numbers from a normal distribution given the standard deviation and the mean, and then computes the confidence interval given the level of significance. Also, a histogram is introduced.

This example uses simulation to find the integral (area under) of a normal distribution curve within a specific interval.

The example computes the European call and put price based on Black-Scholes option pricing models. Cumulative Standard Normal Distribution is also introduced.

The example computes the European call and put price based on Binomial option pricing models. Binomial coefficient is also computed.

This tutorial demonstrates on how to obtain the optimal portfolio (highest return with lowest standard deviation) using Harry Markowitz theory. The largest Sharpe Ratio is used to determine the optimal set. The efficient frontier is plotted from the simulated sample.

Using matrix algebra by utilizing the Excel functions such as, MInverse( ) and MMult( ), which performs matrix inversion and matrix multiplication, respectively.

Use resampling with replacement, a probability distribution for the median is created, along with the standard deviation of the median, which cannot be computed under mathematical formula (since there is none).

This example is perhaps the most advanced example so far on this website. It generates multivariate standard normal distribution deviates from correlated variables and then compute the probability from the given z values. A numerical procedure, Jocobi search method is used to derive the Eigenvectors and Eigenvalues.

This tutorial is two of the most popular tutorials on this site (guess which is the other one?). By assuming underlying probability distributions (normal, uniform, and truncate normal) of the variables in the profit equation, we get a probability distribution for the profit. It answers the questions like 'what is the chance that we will loss profit' and 'what is the chance that we will make the X amount of profit.'

This tutorial contains option sensitivities (delta, gamma, vega, theta, and rho) formulas and source code. Option sensitivities are also know as the Greeks. They measures how sensitive the option price is toward changes in its parameters. All Greeks are available in user-defined VBA functions and in mathematical formulas.