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