ChE 160 SI Becca Fall 2016 Introduction Defineexplain the following terms MsgBox InputBox Option Explicit Dim As amp ampersand xlsm Introduction Answers ID: 714991
Download Presentation The PPT/PDF document "Final Exam Review Part 4 - VBA" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Final Exam ReviewPart 4 - VBA
ChE
160 SI – Becca
Fall 2016Slide2
Introduction
Define/explain the following
terms:
MsgBox
()
InputBox
()
‘
“
Option Explicit
Dim ___ As ___
&
ampersand
.
xlsmSlide3
Introduction- Answers
MsgBox
()
output/display from file
InputBox
()
input information into VBA
‘
single apostrophe
comment
“ double apostrophe
string
Option Explicit
have to dimension/define all variables as their data types
Dim ___ As ___
how to dimension variables
&
ampersand
concatenate/piece together strings and variables
.
xlsm
how to save Macro filesSlide4
&&& Ampersand Practice &&&Slide5
Common Errors
Missing
or misplaced “___”
Misspell
MsgBox
() or
InputBox
()Forgot to Dim variable As datatypeSlide6
Loops
Write out the basic structure of these loops
If loop
For loop
Do loopSlide7
If Loop- Answers
If
<conditional statement> Then
(calculations/commands)
ElseIf
<another conditional statement> Then
(Calculations/commands)
Else
(calculations/commands)
End IfSlide8
For Loop- Answers
For
<counter> = ___ To ___ Step ____
(calculations/commands)
Next <counter>Slide9
Do Loop- Answers
Do
(calculations/commands)
If <conditional statement> Then Exit Do
(calculations/commands)
LoopSlide10
Operators
Define the conditional operators
=
<>
<
>
<=
>=Slide11
Operators- Answers
Con
diti
onal Operators
=
equal to
<>
not equal to
<
less than
>
greater than
<=
less than or equal to
>=
greater than or equal toSlide12
Subroutines and Functions
Difference between sub routine and function
Difference between
ByRef
and
ByVal
Basic structure of a function
Basic structure of a subroutineSlide13
Subroutines- Answers
Difference between sub routine and function
Sub does not return anything, function gives a final result. Also, subroutines are called
Difference between
ByRef
and
ByVal
ByRef
= By Reference, takes the reference variable and changes the value everywhere
ByVal
= By Value, only uses the values locallySlide14
Subroutine and Function Structure- Answers
Basic structure of a function
Function <
functionname
> (<
ByVal
or
ByRef
> <
variablename
> As <data type>, …….) As <data type>
(statements and calculations)
<function name> = <expression>
NOTE: function has function name as final variable which will be the answer displayed when run.
Basic structure of a subroutine
Sub <
subroutinename
> (<
ByVal
or
ByRef
> <
variablename
> As <
variabletype
>, ….)
(statements with variables in argument list and declared in subprogram)
End subSlide15
Inputs and Outputs
Output vs Append vs Input
Print vs Write vs InputSlide16
Inputs and Outputs- Answers
Output
Append
Input
Open (pathname\filename) For Output As #
filenumber
Open (pathname\filename) For Append As #
filenumber
Open (pathanme\filename) For Input As #filenumber
VBA will create
the
file if one doesn't exist
VBA will create the file if one doesn't exist
Has to exist before opening
Write over everything in the file
Adds onto a file
Brings information into VBA
Ends with Close #filenumber
Ends with Close #filenumber
Ends with Close #
filenumberSlide17
Inputs and Outputs- Answers
Print
Write
Input
Print #
filenumber
, [
outputlist
]
Write #
filenumber
, [
outputlist
, comma delimited]
Input #filenumber, variablelist
Variables can be separated using semicolons or commas
Values have to be separated by commas
Values have to be designated as correct data type to be inputted ( "" around strings, everything separated by commas)
Comma separates by tabs
Write file has commas still in between data types
Semicolon separates by spacesSlide18
Arrays
How do you create an array using VBA?Slide19
Arrays- Answers
For Count = 1 To 11
Column(Count, 1) = 2 * Count - 1
Next
CountSlide20
How did that go?