Visual Basic for
Applications (VBA) - Programming
|
||||
|
|
|
|
|
?
|
First Program Steps
First steps to get programming
|
Making Decisions based on users Input
|
Using VBA Programming in Access97
Database
|
First Program
View ONLY after reading
'First Program Steps'
|
Moving & Selecting
from an unknown position
|
PCR Logic Club test
|
First
steps to learning programming - Running & Assigning a macro
The simplest program could be a recorded
macro, using Options, Macros,
Record New Macro.
Example: In a blank Excel File:
Click on the 'Sheet2' tab
Start the macro recorder: Options,Macro,Record
New Macro
Type a name into the 'Macro name:' window
of the 'Record Macro' Box
Click into Cell B12, enter: Mon
in C12 enter: Tue In D12 enter: Wed
Click the Macro Stop button or use Options,
Macro, Stop Recording
These three enter actions are now
recorded within the macro as a VBA procedure.
Next, delete the 3 entries you placed in
B12, C12, D12
Having done this you must next draw a
command button - use View, Toolbars, Forms (or Drawing Tools in the previous
versions of Word) - and assign your new macro to it.
Selecting and drawing the button: Click on
the Button tool in the Forms toolbar. At the top central portion of the
your sheet, drag down and across to form the button. As soon as you have done
this the 'Assign Macro' box will appear. Select the macro name of the
macro just recorded and click Ok. Finally replace the name in the button (e.g.
'Button1') by typing in your own name for the button e.g: PutDates
Click the button and hey presto ! the
entries magically re-appears but very much faster than you could every have
entered them.
Taking a look at the macro
to learn some basic programming
Use Options, Macro, Visual Basic Editor
In the 'Project - VBA Project' window, click on Module1, then click the View tool at the
top of the window (first on left). A code window now appears on the right.
Click the full box tool to enlarge the window.
The top entry in the window will be:
Sub <yourmacroname> () where
<yourmacroname> is the name you gave to the macro as you recorded it.
Ignore the green entries these are
comments and carry no further function, being ignored when the procedure runs.
The end of the procedure finishes with:
End Sub.
Notice: the 'ActiveCell.FormulaRxCx = " " and the 'Range(" ").Select
entries.
The expression on the right in this case
is a string expression (formula, numeric or alphanumeric) denoted by the "
"
ActiveCell.Formula is used for text or for a cell formatted for a
formula e.g: +A6 + A9
In this case, for text you could replace
it with ActiveCell.Text or ActiveCell.Value
The expression on the right ( "
") is assigned to the active cell (the cell where the cursor is at
present, or the cell which is selected at the time).
If the expression on the right was for a
cell formatted as purely numeric, you would use: ActiveCell.Value.
The Range(" "). Select entry is the same as the action of clicking into a specific cell, thus
typing Range("A1").Select into the procedure, would result in cell A1 being
selected on running the procedure - A1 would be made the active cell.
Making a real Program -
Adding variable user input as the procedure runs
Say you wanted to put any value into cell
B12 instead of it always being Mon.
This would make the procedure much more
useful for everyday purposes and introduce a real programming command to change
the procedure from just a Macro to a small program.
This is done by using the InputBox
statement:
<Variablename> =
InputBox("<message>","<title of box
>","<default value",<position>,<position>)
Example: Dayone = InputBox("Enter the first day
name","DAY ONE","Mon",100,1)
At run time this will show an input box:
Whatever the user puts in the input box
will be stored in the variable Dayone
Now the variable holds the value of the
input, but that input is there held by the program only, not in the sheet.
Here's where the an ActiveCell statement comes in
In this case, if the active cell was B12,
then:
ActiveCell.Value = Dayone
would place the value input by the user
into B12.
(to retrieve a value FROM a cell to a
variable in the program, reverse the statement above:
Dayone = ActiveCell.Value)
Finally, to improve the whole
thing, have a macro button in Sheet1 instead, with the result of the program in
B12 of Sheet2.
This introduces a Sheet Select statement ActiveSheet(" ").Select
Here's the final program with comments with each line:
Sub Macro1()
ActiveSheet("Sheet2").Select 'Select Sheet1
|
|
Range("B12").Select 'Select B12
|
|
Dayone = InputBox("Enter the first day
name","DAY ONE","Mon",100,1) ' Get user input
|
|
ActiveCell.Value = Dayone ' place it in cell
|
|
End Sub
|
|
Making
Decisions based on users Input
In manual operation of Excel you have the
IF function to make a decision based on another/other cells in the worksheet
(see Excel
- Decisions -What If).
Likewise in VBA you can have the same
functionailty inside a procedure:
If <condition> = <condition> Then
do something
Go to <label> ............. or you could use Exit Sub to finish the program
End If
Examples:
Green text is comment only and
has no part of the program
Example 1 - Get a name from the user
and check it for length (no of characters)
CName$ = InputBox("input
customer Name", "CUSTOMER NAME", CName$, 100, 1) 'get customer name
CnLen = Len(CName$) ' get length of customer name
If Len(CName$) > 24 Then ' if customer name more than 24
characters
MsgBox "Name too long, please abbreviate" ' give this message
GoTo Cnmeagn ' and go to the part of the program identified by the label Cnmeagn,
carry on from there
End If
' carry on with program if
characters = or less than 24
Example 2 - Get a name from the user
and check to see if a space has been added after it - (which would cause it not
to be found later)
CName$ = InputBox("input
customer Name", "CUSTOMER NAME", CName$, 100, 1) 'get customer name
If Right$(CName$, 1) = " " Then ' is there a space ( " ") to the right of the
name
MsgBox "Trailing spaces are not
allowed" ' there is so
give this message
GoTo Cnmeagn ' and goto the part of the program identified by this label,
carry on from there
End If
' carry on down program
if no space on right of input
Example 3 - Get a date (but give user
option to browse sheet for existing entries)
Tddate = InputBox("Input
today's date (c = cancel b=browse)", "TRANS DATE", Tddate1, 100,
1) ' get date
If Tddate = "b" Or Tddate = "B" Then ' if user inputs b or B
Call Doscroll ' go to part of the program I called Doscroll (for
scrolling function), execute it and return to the line below this
GoTo Tddateagn ' after doing Doscroll go to part of the program identified
by this label, carry on from there
End If
Moving & Selecting from an unknown position
Moving with stating a cell reference:
You can select a cell by using the Range
statement eg: Range("B15").Select
However, it is likely that new rows for
new records , or columns may be added, or a sort may have been done - the data
in B15 will now be in a different cell.
How could you move from the new current
location without having to state the actual cell location ????
This is done by using the Offset method of
the ActiveCell e.g:
ActiveCell.Offset(0,1).Select
The 1st figure in the brackets
deals with rows
The 2nd figure after the comma,
deals with columns
Positive numbers move down and right
Negative numbers move up and left
e.g: ActiveCell.Offset(1,2).Select - Move 1 row down, 2 columns to right
ActiveCell.Offset(-1,-2).Select - Move 1 row up, 2 columns to left
Finding a set point in a sheet:
A problem arises when you have to leave
one location for another but need to return to the original location later.
a. If you can do this within the same
procedure (e.g without exiting the current program), by storing the address of
the original location.
b. If you leave a location and need to
return after the procedure has ended. E.g: re-running the program later or from
a different program, then you can leave a marker in the sheet.
Case a.
LastLoc = ActiveCell.Address
LastLoc will hold the cell
reference for the current cell. Now you can move anywhere and return by:
Range(LastLoc).Select
Case b.
Leave a text entry in an obvious place
(e.g after the last data row, at the end of a column)
Eg: EndRecords
When you re-run the program use Offset and
the IF statements to locate EndRecords. E.g:
Range("D1").select - Start at the top of a column
Downagain:
ActiveCell.Offset(1,0).Select - Go down one row
If ActiveCell.Value = "" then - Make sure the cell is not blank -
(dont leave blank rows before data)
MsgBox("marker lost")
Exit Sub
End If
If activeCell.Value <>
"EndRecords" then - Check for marker
Goto Downagain - Not found go back to offset again
End If
' marker will be found at this
point, carryon with program
Logic
Club Test
Complete the logical test below
and qualify for club membership
Select the test and instructions shown
below.
Use File, Print
or
Edit, Copy, paste into Excel or Word and
Print.
With the printout you can work on it
off-line:
TEST 001
Convert all the letters to numbers.
....but a letter cannot have two numbers,
it must have its own unique number.
When converted add 'Sunny' + 'Sunny' +
'Days' to equal 'AGAIN'
Clue:
'Isn’t the letter S odd’
S
|
U
|
N
|
N
|
Y
|
S
|
U
|
N
|
N
|
Y
|
|
|
D
|
A
|
Y
|
S
|
A
|
G
|
A
|
I
|
N
|
mail your solution to me
|
|
|
|
|
to Spreadsheets
mailto:michael_small@lineone.net
|
|
|
