Transcript VBAIntroduction - Haaga

VBA
(Visual Basic for Applications)
What is Excel, just a spreadsheet?
Time for demos...
by Christian Brade
1
24.3.2017
Time for demos...






Snake game
Speed game
SMS sender
Excel with movies
Sudoku solver
Etc.
2
24.3.2017
What is a macro
 Small computer program, that needs to run within
another program.
 Collection of instructions for the computer.
 Automates a task for you.
 What you would normally need several clicks for can now be
done with one click.
 Reduces the risk of errors.
 Control hole computer, not just MsExcel tasks.
3
24.3.2017
Recording macros
 MS Office has a built-in recorder.
 You can record anything you can do yourself in Excel
(in theory).
 You can use the recorder without knowing any VBA
commands.
 You can edit marcros in VBE (Visual Basic Editor)
 Macros will be generated to Modules in VBE
4
24.3.2017
Excercise 1
Start recording
Type your name to cell E1
Autowidth column E (doubleclick)
Stop recording
Check the macro from module1
Clear your name from cell E1 and resize the column to
original width
 Run the macro






5
24.3.2017
Excercise 2
Start recording
Type your name to cells E1, F2 and G3
Autowidth columns E, F, and G
Stop recording
Try to shorten the code as much as possible in the
module
 Clear your name from cells and resize the columns to
original width. Run the macro.





6
24.3.2017
VBA –programming: Basic elements




If -sentence
Loop -structures
Event-Run programs
Microsoft Visual Basic Help
<F1>
 ’ –to comment lines in a code (apostrophe)
7
24.3.2017
VBA –programming: Basic elements
 If -sentence
If value = true Then
do this
Else*
do that*
End If
Workbook If(value=true;this;that)
*Voluntary
8
24.3.2017
VBA –programming: Basic elements
 Enhanced If -sentence
If value = 1 Then
MsgBox "1.choise”
ElseIf value = 2 Then
MsgBox "2.choise"
Else
MsgBox ”No choise”
End If
9
24.3.2017
VBA –programming: Basic elements
 Excercise III
 Code a macro what reads a number from the cell A1 and
multiplies it by 2 If the number is smaller than 100.
 Code the program to Module and name it ExcerciseIII
Sub ExcerciseIII
…
End Sub
10
24.3.2017
Assigning macros to buttons
 View – Toolbars – Form – Button
 Click and draw -> Add macro
11
24.3.2017
VBA –programming: Basic elements
 Excercise IIIb
 Assign a button to excercise III
12
24.3.2017
VBA –programming: Basic elements
 Enhanced If –sentence (And and Or)
If value = 1 Or value = 2 Then
MsgBox ”Value is 1 or 2”
ElseIf value >= 2 And value <=3 Then
MsgBox ”Value is between 2 and 3”
Else
MsgBox ”No value”
End If
13
24.3.2017
VBA –programming: Basic elements
 Excercise IV
 Code a macro what reads numbers from the cells A1 and B1.
 If B1 is greater than A1 change the values. A1 gets the value
of B1 and B1 gets the value of A1.
 If B1 is not greater than A1, divide the numbers by 2.
 The macro will be started from a button.
14
24.3.2017
VBA –programming: Basic elements
 Do Loop While
i=1
Do
Range("A" & i).Value = i
i=i+1
Loop While i < 100
 What does the loop do?
15
24.3.2017
VBA –programming: Basic elements
 For Next
For i = 1 To 100
Range("A" & i).Value = i
Next
 What does the loop do?
 To quit eternal loop <Ctrl+Break>
16
24.3.2017
VBA –programming: Basic elements
 Excercise V
 Code a macro what types the multiplication table of number 3
to 100.
 The macro will be started from a button.
1*3=3
2*3=6
3*3=9
…
100 * 3 = 300
17
24.3.2017
VBA –programming: Basic elements
 Excercise VI
 Code a macro what types to column N, row 1 one *,
to row 2 ** and finally to row 10 **********.
 The macro will be started from a button.
*
**
***
…
**********
18
24.3.2017
Visual Basic Editor (VBE)
Alt+F11
Place for Event-Run Macros to each
Worksheet (Like Worksheet_Change )
Place for Event-Run Macros to whole
Workbook (Like Workbook_Open)
Place for user-defined Macros and function
+ global variables (Like HelloWorld)
Creating the Code. What do We Need?




Understand the MS Office object model
Access to the Visual Basic Editor VBE
Basic programming skills
Motivation
20
24.3.2017
The Object Model. Why bother?
 Excel (and the other Office applications) consist of a
number of objects
 Macro programming is actually just manipulation of
these objects
 When you understand the basic principle, it’s easier to
learn how to program other Office programs
 Press a dot after the object and you will the subobjects,
properties and methods
21
24.3.2017
Definition of an object
 An object is a named part of a computers memory
 It has certain features or properties, like some people
have brown hair, others red hair etc.
 It has some functionality or methods, like people are
able to walk or cars able to drive
 Objects can contain other objects
22
24.3.2017
An example – A box
Object properties
•
•
•
Size
Color
Position
The object might have a move method.
23
24.3.2017
The Objects in Excel





The mother object is called an ”Application”
An application contains a number of workbooks.
These workbooks contain sheets.
The sheets contain ranges etc.
Application, Workbooks etc. contain many other
objects, properties and methods.
24
24.3.2017
Excercise VII
 Try to find out who is the user of Excel (=application)
at the moment.
 Find a function ”at the moment”
 Use MsgBox to show info to the user
 Start the Macro from button
25
24.3.2017
Event-run Macros
 Normal Macros are run manually
 Run from Buttons, Shortcuts
 Event-run macros will be started automatically. They
are started from an event (like Workbook_Open())
 Major virus alert! If you open an application with
Workbook_Open() Macro, the macro will be run.
26
24.3.2017
Security levels
Tools – Macros –
Security
If your macro
doesn’t work in
another computer,
try to choose
Medium level.
Never Low!!!
27
24.3.2017
Excercise VIII
 Code a macro that requires a user’s name and types it
to cell A1 when entering to WorkSheet 1
 Use InputBox()
28
24.3.2017
Excercise IX
 Code a macro that automatically saves the file when
closing it
29
24.3.2017
Private Sub Workbook_Open()
Dim Users(2) As String
Dim Found as Boolean
Found=False
Users(0) = "Christian Brade"
Users(1) = "Elvis Presley"
Users(2) = "George Bush"
For i = 0 To UBound(Users)
If Application.UserName = Users(i) Then
Found=True
End If
Next
If Found=False Then
Application.ActiveWorkbook.Close
End if
End Sub
30
24.3.2017
Final advice
 If you wonder how a certain operation is done, record it
and look it from the code.
 Place the cursor over a keyword and press F1 for help
on that word.
31
24.3.2017
Thank You!
32
24.3.2017