VBA Example Files and Code (A
work in progress)
My advice is for you to download
the files directly to your hard drive by right-clicking on the link and
then selecting "Save Target As..." - otherwise the Excel files
may not work fully in your browser window!
To see the programming in these files first open the file and then open the
VBA Editor (Press ALT + F11) to view the code.
- Interactive Time Series:
(41KB)
This Excel 2000 file calculates a moving average (The period of which can
be changed) from a set of data that can be extended or reduced if required.
This is possible since the data has been set up as a dynamic
named range. (No documentation - sorry)
- Numerical
Iterations: (100KB) - (UPDATED 03/10/01)
This Excel 97/2000 file allows the user to numerically solve equations by
using either of the following methods:
(1). The Fixed Point Method,
(2). The Secant or Linear Interpolation Method,
(3). The Bisection Method,
(4). The Newton-Raphson Method
What is useful here is that the user simply enters the equation into a particular
cell on the worksheet (but without an equal sign in front of the equation)
together with either one or two initial conditions in further cells. Then
the VBA Code "reads" the required equation and evaluates it at the
initial conditions. To then produce further iterations a button is pressed
on the worksheet. Thus no copying down of formulae is required.
The idea behind the code came from a VERY useful file, DemoFunctionsInVBA
(36KB), kindly given to me by John Stanley in which a variety of functions
and the many ways in which arguments are passed into them are investigated.
- UserForm Example: (393KB)
This Excel 97/2000 file displays accustom built UserForm that interacts with
the Workbook in a variety of ways. It uses spinner controls, slider controls,
option buttons, check buttons, pictures, listboxes, comboboxes, hidden parts
of the form, etc.
See what it does and then look at the code by opening the Visual Basic Editor
(Press ALT + F11). Look also at the Named Ranges in the Worksheet by clicking
"Insert --> Name --> Define".
- Random Number Generator
(35KB)
This Excel 2000 file gives the user two ways in which to calculate a column
list of random numbers.
Method 1: Here the user chooses an upper value, lower value and the
total number of random numbers required in the chosen range - repetitions
of numbers are allowed.
Method 2: Here the user chooses an upper value and a lower value but
now the program returns only these numbers in the chosen range once - that
is without repetition.
This file is particularly useful in so far as it returns the VALUES only of
the Random numbers and not cell values based on the RAND or RANDBETWEEN Functions
which would then change their values each time the worksheet were recalculated.
See my Code Transferability Tutorial which
relates to this example in particular but has far reaching applications for
many other VBA projects.
Links