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.

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

Introductory and
immediate VBA examples and tutorials.

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

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

Excel examples without using VBA.

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):

Copyright ©

Anthony Sun

All Rights Reserved.

Anthony Sun

All Rights Reserved.

Standard Deviation** | **Median** | **Mean** | **Skewness and Kurtosis** | **Lotto Number Generator** | **Playing
Card Probability and Hypergeometric distribution** | **Monte Carlo Integration and risk analysis** | **Black-Scholes Option Pricing
Model - European Call and Put ** | **User-defined customized function** | **Binomial Option Pricing Model** | **Portfolio
Optimization** | **Multiple Regression** | **Bootstrap** | **Multivariate Probability Distribution** | **Monte Carlo Simulation** |**Option Sensitivities (Greeks)** | **Frequency Histogram** | **Creating and Managing Array** | **finding Max and Min in Array** |**decision structure** | **If and Select Case** | **Binomial Coefficient** | **Sum Number in an Array** | **sorting** | **resampling
without replacement** | **factorial** | **randomize** | **random number generator** | **Cumulative Standard Normal Distribution** |**declare an array** | **resize an array** | **manage dynamic array** | **create multi-dimensional array** | **find the array size** |**For...Next with Step** | **Do...While...Loop** | **Do Until...Loop** | **call a sort sub procedure** | **Percentile and Confidence
Interval** | **Chi-Square** | **F Distribution** | **normal** | **Student-t** | **Lognormal** | **Log Pearson III Distribution** |**Gamma Distribution** | **Beta Distribution** | **Hypergeometric Distribution** | **Triangular Distribution, Binomial Distribution** |**Multiviate Standard Normal Distribution** | **Numerical Searching Method** | **Newton-Ralphson** | **Secant method** | **Bisection
method** | **Implied Volatility** | **Creating a Histogram** | **Option on asset with cash dividend** | **option on asset with
continuous dividend (stock index)** | **option on futures** | **option on currency** **

.

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.

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.

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

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.

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.

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

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.

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

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.

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

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.

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

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.

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

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

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

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

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

Simulation Basic Tutorial Series

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

Two of the mostly used statistic estimates in action.

Second tier statistic estimates.

Compute the simulated values at a specific percentile.

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

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

Compute the median from an array

Generate
uniform random number within a range.

Sum up number in a array using loop.

Compute
binomial coefficient.

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

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 utilizing**sorting** 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.