Download VBA Cheat Sheet and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity!
Dim B, C As Byte
B is Variant, C is 0.. Boolean^
True (<> 0), False (=0) Integer^
16 bit, -32,786 .. 32, Long^
32 bit integer, -2.14E9 .. 2.14E Currency^
64 bit integer / 10, Single^
32 bit, -3.4E38 .. 3.4E38, 6 digits Double^
64 bit, -1.8E308 .. 1.8E308, 14 digits Date^
Double, days since 30. Dec 1899, 0: Object^
Reference to any object Form^
Reference to any Form Variant^
Any of the types or Null, Empty, Nothing, Error - plus a type tag. All
database fields
are Variant
String^
Variable length, max 2E9 characters String * 50^
Fixed length, space filled Initial values
String = “”, Boolean = False Number, date = 0
Database field = Null Object = Nothing
Variant = Empty Dim c(5, 1 To 6) As t
Same as c(0..5, 1..6) Dim d( ) As Single
Dynamic array declaration ReDim d(5, 1 To 6)
Statement Index range (re)defined, data lostReDim Preserve d(5, 1 To 8)Last index range redefined, data preservedErase d^ Releases memory for dynamic arrayType Customer^
Simple modules only custID As LongcustName As String * 50custAddress As StringEnd TypeDim custTable(20) As Customer
Declarations
i = i+2 ‘ Comments =^ “long text A” & _“long text B”
‘ Comment in last line only Set f = Forms(0)
Store a reference Set f = New Form_frmG
Create object, store ref Set f = Nothing
Line continuation, comments, assignment^ Delete object if last ref
If a=1 Then c=d+
Single statement If a=1 Thenc=d+...^
Multiple statements ElseIf a=2 Thenc=d / 2.. .Elsec=0.. .End IfSelect Case zipCase 4000type = a.. .Case 4001, 5000 To 5999type = b.. .Case Elsetype = c.. .End SelectOn Error Resume Next
Ignore error
... If Err > 0 Then...
Test for error On Error GoTo fail
Enable error handler
.. .fail: MsgBox(... )
Continue here at error On Error GoTo 0
Conditional statements OptionalOptional Optional^ Let VBA handle errors
While a<^
May be empty loop c=c*2...^
Exit not allowed WendDo While a<
May be empty loopc=c*2... Exit Do Exit optional... LoopDo^
Loop at least once c=c*2... Exit Do^
Exit optional
.. .Loop While a<10For i=1 To last Step 2
Step optional c=c*^
May be empty loop
... Exit For^
Exit optional
.. .Next iDon’t trust value of i when loop ends without ExitFor Each f In Forms
Scan collection call print(f.name... )... Exit For^
Exit optional
.. .Next
Loops
proc a, b, , d^
Parenthesis-free notation Call show(a, b, , d)
Subroutines only res = fnc(a, b, , d)
Functions only Sub show(a, b As t, Optional c, d)If IsMissing(c) Then.. .Exit Sub^
Optional
.. .End SubFunction fnc(a, b As t, Optional c, d) As String
As String is optional If IsMissing(c) Then.. .fnc= result.. .Exit Function^
Exit optional
Procedures = Subroutines and Functions.. .End Function Dim a^
Visible in this module only Public b^
Visible to all modules Private Sub show(p)
Visible in this module only Dim c^
Visible in this sub only Static d^
Visible in this sub only,
...^
but survives calls End SubPublic Sub show(p)
Visible to all modules Dim c^
Visible in this sub only
Module and Scope.. .End Sub
23, -23, 0, -4.9E-
Decimal numbers &h09A0FF, &o
Hex and Octal, color: BGR “Letter to:”^
Strings Chr(65), Chr(vbKeyA)
The text “A” “John” & Chr(10) & “Doe”
Two-lines, Chr(10)=new line “Don’t say “”no”” “
Don’t say “no” “select * from g where a=‘simpson’ ;”Single quotes are suited for SQLTrue, False^
BooleansDate/time #10/24/02#^
24th Oct 2002 #10/24/02 14:15:00#
24th Oct 02 at 14: #10/24/02 2:15 pm#
24th Oct 02 at 14: Null, Empty^
Special values Nothing^
Object reference to nothing Constant declaration Const max=10, start=#3/24/2#
Constants
Forms(i)^
Element in collection Forms(“frmCst” & i)Forms!frmCst
Bang-operator Me.Name, Me!name
Property~Control in module Me.subLst.Form.name
Property in subform Me.Parent.txtName
Control in main form basCommon.simDate
Variable in foreign module c(row, col)^
Indexing an array custTable(i).custID
Field in array of records With Me.Recordset
Apply before dot and bang .addr = .addr & zip!name = Null.MoveNext.. .End With
Addressing
Errors:^ “Invalid use of Null” for Null parametersOverflow or type mismatch for bad parameters.CByte(“37”)^
=37. Overflow outside 0.. CInt(“2.6”)^
Round(2.6)^
= 3.0000 (Double)Rounding down: See Math functions Int, Fix. CLng(“99456”)
CCur(1/3)^
=0.3333 (always 4 decimals) CSng(“-2.6e-2”)
CDbl(“-2.6”)^
CDbl(#12/31/1899#)
CDate(“23-10-03”)
= #10/23/2003# (as Double)Uses regional setting for input format CDate(1)^
CStr(23)^
= “23”. No preceding space. Str(23)^
= “ 23”. Preceding space when >= 0 CStr(#10/23/2003#)
= “23-10-03”Converts to regional date format CVar(X)^
Simple conversion functions = X As Variant. X may be Null
Null parameters:
A Null string as input will give the result Null. Null as another parameter is an error.Asc(“AB”)^
= 65, Ascii code for first character Chr(65)^
= “A”, a one-letter string with thisascii character Len(“A_B”)^
= 3, length of string. Left(“abc”, 2)^
= “ab”, leftmost two characters Left(“abc”, 8)^
= “abc”, as many as available Right(“abc”, 2)
= “bc”, rightmost two characters Mid(“abcdef”, 2, 3) = “bcd”, three chars, chars 2-4LTrim(“ ab ”)^
= “ab ”, leading spaces removed RTrim(“ ab “)^
= “ ab”, trailing spaces removed Trim(“ ab “)^
= “ab”, leading and trailing removed Lcase(“A-b”)^
= “a-b”, lower case of all letters Ucase(“A-b”)^
= “A-B”, upper case of all letters Space(5)^
= String of 5 spaces Option Compare
Text | Binary | Database Option in start of module. Text: string comparison iscase insensitive and follows regional settings.Binary: comparison is based on the internal ASCII code.Database: comparison is defined by the SQL-engine.StrComp(“ab”, “abc”)
= -1, first string smallest StrComp(“ab”, “ab”)
= 0, strings equal StrComp(“ac”, “abc”)
= 1, first string largest If “ab” < “abc”...
String functions Works just as well
Iif(a=a, b, c)^
= b Iif(a<>a, b, c) = c Iif(Null, b, c)^
= c Choose(2, a, b, c)
= b Choose(4, a, b, c)
= Null Choose(Null, a, b, c)
Iif and Choose^ Error
LBound(d)^
Lower bound for first index LBound(d, 2)^
Lower bound for second index UBound(d)^
Upper bound for first index UBound(d, 3)
Array bounds Upper bound for third index
Page 4 - VBA Reference card
Nulls : Any Null operand gives a Null result, except.. .^^ Exponentiation-^ Unary minus, 2-3 = -6^ Multiply, Result type is Integer, Double, etc./^ Divide, Single or Double result^ Integer divide, result truncated, 5\3 = 1Mod^ Modulus (remainder), 5 Mod 3 = 2+ -^ Add and subtract&^ Concatenation, String result (local date format)= <> < > <= >=
Equal, unequal, less than, etc. Is^ Compare two object references, e.g.If r Is Nothing Test for nil-reference Partition(22, 0, 100, 10)
a Between 3 and 9
Not in VBA, okay in SQL a IN (2, 3, 5, 7)
Not in VBA, okay in SQL Not^ Negation. Bit-wise negation for integersAnd^ Logical And. Bit-wise And of integersOr^ Logical Or. Bit-wise Or of integersX^ Exclusive Or. Bitwise on integersEqv^ Logical equivalence. Bitwise on integersImp^ Logical implication. Bitwise on integerss Like “s?n” Wildcard compare.
?^ any char here. #^ any digit here.
*****^ any char sequence here
Operators, decreasing precedence
VBA Reference Card
DLookup(“name”, “tblGuest”, “guestID=7”)= name of guest with guestID=7.All three parameters are texts inserted into SQL.DMin(“roomID”, “tblRooms”, “roomType=2”)= smallest room number among double rooms.DMax, DSum, DCount, DAvgSimilar, just finds largest, sum, number of, average.Null treatment, see SQL.
DLookup, DMin, etc.
Sqr(x)^ Square root of x. Sqr(9) = 3.Sin(x), Cos(x), Tan(x), Atn(x)
Trigonometric functions. X measured in radian (180 degrees =
π^ =
Math functions 3.141592 radian)Sin(0) = 0, Sin(3.141592 / 2) = 1Exp(x) e to the power of x (e = 2.7182...)Log(x) Natural logarithm of x. Log(e) = 1.Rnd( ) A random number between 0 and 1.Type is Single.Abs(x) Returns x for x>=0, -x otherwise.Sgn(x) Returns 1 for x>0, 0 for x=0, -1 for x<0Int(x) Rounds x down to nearest integral valueFix(x) Rounds x towards zeroHex(x) Returns a string with the hexadecimalvalue of x. Hex(31) = “1F”Oct(x) Returns a string with the octal value of x.Oct(31) = “37”
A date value is technically a Double. The integerpart is the number of days since 12/30-1899, 0:00. Thefractional part is the time within the day.Several functions accept date parameters as well asstring parameters that represent a date and/or time. Null parameters:
Always give the result Null. Now( )^
= current system date and time Date( )^
= current date, integral date part Time( )^
= current time, fractional date part Timer( )^
= Number of seconds sincemidnight, with fractional seconds. Date =...^
Sets current system date Time =...^
Sets current system time DateSerial(2002, 12, 25)
TimeSerial(12, 28, 48)
= 0.52 (Time 12:28:48) Day(#12/25/02#)
= 25, the day as Integer Month(#12/25/02#)
= 12, the month as Integer Year(#12/25/02#)
= 2002, the year as Integer Weekday(#12/25/02#)
= 4 (Sunday=1) Hour(35656.52)
= 12 (Time 12:28:48) Minute(35656.52)
Second(35656.52)
Date and time functions^ = 48
MsgBox(“Text”, vbYesNo+vbCritical) =vbYesAlso: vbInformation, vbQuestion, vbExclamation
MsgBox
Returns True if v is declared with the type tested for, is aVariant currently with this type, or is a constant of thistype. IsDate and IsNumeric also test whether v is a textthat can be converted to that type.IsArray(v)^
Tests for any type of array IsDate(v)^
Tests whether v is a date or a stringthat can be converted to a date IsEmpty(v)^
Tests whether v is unallocated(Strings of length 0 are not Empty) IsError (v)^
Tests whether v is an error code IsMissing (v)^
Tests whether v is a parameter thatis missing in the current call. IsNull (v)^
Tests whether v is of type Null.(Strings of length 0 are not Null) IsNumeric(v)^
Tests whether v is a numeric type(Byte, Integer, Currency, etc.) or astring that can be converted to anumeric type. IsObject(v)^
Tests whether v is a reference toan object, for instance a Form. Truealso if v is Nothing (the nil-pointer) VarType(v)^
Integer showing the type: 0 vbEmpty
vbString 1 vbNull^
9 vbObject 2 vbInteger
vbError 3 vbLong^
11 vbBoolean 4 vbSingle
12 vbVariant (array) 5 vbDouble
vbByte 6 vbCurrency
vbUserDefinedType 7 vbDate^
Type check functions 8192 vbArray (added)
NPV(0.12, d( ) ) The array d must be of type Doubleand contain a list of payments. Returns the netpresent value of these payments at an interestrate of 0.12, i.e. 12%.IRR(d( )) The array d must be of type Double andcontain a list of payments. Returns the internalrate of return, i.e. the interest rate at which thesepayments would have a net present value of 0. Ifthe list of payments have many changes of sign,there are many answers, but IRR returns onlyone.IRR(d( ), 0.1) The second parameter is a guess at theinterest rate, to allow IRR to find a reasonableresult.SYD, NPer and many other financial functions areavailable for finding depreciated values, numberof periods to pay a loan back, etc.
Financial functions
Dim rs As Recordset, clone As Recordset, Dim A( )s = “SELECT *... “
Or “tblCustomer” Set rs = CurrentDB.OpenRecordset(s)Set clone = rs.CloneWhile Not rs.EOF
EndOfFile (BOF similar) rs.Edit^ (or rs.AddNew)
Prepare edit buffer rs! fieldX =...
Change edit buffet rs.Update^
Update current record
.. .rs.Delete^
Delete current record rs.MoveNext^
Not after AddNew WendA = rs.GetRows(n)
Copy n rows to A A(0, 3)^
First field of 4th record rs.Close Other properties: rs.AbsolutePosition = 0rs.Bookmark = clone.Bookmarkrs.Move(n)^ Move
current^ n records back/forward rs.MoveNext^
... MovePrevious, MoveFirst, MoveLast rs.FindFirst(“a=‘simp’ ”) ... FindPrevious, FindNext, FindLast rs.NoMatch^
True if Find didn’t succeed rs.Requery^
Re-compute query after changes rs.RecordCount Number of records currently loaded
by database engine rs.Name^
String, SQL-statement for query, readonly rs.DateCreated, rs.LastUpdated Only for tables
Record set DAO 3.
SELECT name, zip FROM tblGuest
WHERE^ ID=2;
SELECT tblTown.name
AS^ address, tblGuest.name FROM tblGuest
INNER JOIN
tblTown ON tblGuest.zip = tblTown.zipWHERE tblGuest.zip = 4000
ORDER BY^ name; Or:^... ORDER BY name, tblGuest.zip
DESC ;
SELECT stayID,
Min (date) AS arrival FROM tblRoomState WHERE state = 1 GROUP BY^ stayID
HAVING^ Min(date) = #4-21-02# ; Null handling:ORDER BY: Null smaller than anything else.Sum, Avg, Min, Max, Var, VarP, StDev, StDevP: Lookat non-null values. Null if all are null.Count: Counts non-null values. Zero if all are null (butNull for Crosstab).SELECT name FROM tblGuest WHERE zip^ IN^ (SELECT zip FROM tblTown WHERE name<“H”);SELECT... WHERE zip
NOT IN^ (1200, 1202, 1205);
SELECT 0, “New” FROM tblDummy^ UNION^ SELECT zip, name FROM tblTown;Concatenates one table (here a single record 0, New)with another table. Field 1 under field 1, etc. UPDATE^ tblGuest
Updates records where... SET name = “John Smith”, zip = 4000 WHERE ID = 2; INSERT INTO^ tblGuest (name, zip) Adds one recordVALUES (“Ahmet Issom”, 5100); INSERT INTO^ tblTemp
Adds many records SELECT * FROM tblGuest WHERE zip=4000; DELETE FROM
SQL tblGuest WHERE ID = 2;
cbo^ Combobox
lbl^ Label
bas^
Module
chk^ Checkbox lst^ Listbox
frm^ Main form cmd^ Button^
mni^ Menu item
fsub^ Subform form ctl^ Other^
sub^ Subform control qry
Query
grp^ Option group
tgl^ Toggle button
qxtb^ Crosstab qry opt^ Option button
txt^ Text control
Other tbl Table
Control prefixes
VBA^ ↔^ Access Alt+F
Select full field
F
Property list^
Ctrl+J^ Zoom window
Shift+F
Constant list^
Ctrl+Sh+J^
Combo open^
Alt+Down
Parameter list
Ctrl+I^
Next Form^
Ctrl+F
Immediate^
Ctrl+G^ Upper/lower section
F
Run^
F5^ Choose menu
Alt
Step into^
F8^ Next menu/tab
Ctrl+Tab
Step over^
Shift+F^ Next application
Alt+Tab
Break loop^ Ctrl+Break^
Update^
(Shift+) F
Object browser
F^
Open properties Alt+Enter Close VBA/Appl
Alt+F4^ Close Form
Ctrl+F
In Form:^ User mode F
General short-cuts Design mode Alt+V+Enter
VBA short-cuts
Page 2 - VBA Reference card
VBA Reference card - page 3
Null allowed for x
Converts a value to a string, based on a format string.Format characters that are not placeholders, are shownas they are. Backslash+character is shown as thecharacter alone, e.g. \d is shown as d. Numeric placeholders 0 Digit, leading and trailing zero okay here#^ Digit, no leading or trailing zero here.^ Decimal point (or regional variant)e- or e+^ Exponent or exponent with plus/minus%^ Show number as percentFormat(2.3, “00.00”)
Format(2.36, “#0.0”)
Format(0.3, “##.0#”)
Format(32448, “(00)00 00”)
Format(32448, “##.#E+”)
= “32.4E+3”
Format(32448, “##.#E-”)
= “32.4E3”
Format(0.5, “#0.0%”)
;^ Separator between formats for positive,negative, zero, and null values:Format(-3, "000;(000);zero;---")
String placeholders @^ Character or space&^ Character or nothing!^ Cut off from leftFormat(“A123”, “@@@@@@”)
= “¬¬A123”
Format(“A123”, “&&&&&&”)
= “A123”
Format(“A123”, “(@@)-@”)
= “(A1)-23”
Format(“A123”, “!(@@)-@”)
Date/time placeholdersExample:^ DT = #2/3/2002 14:07:09# (Sunday)Format(DT, “yyyy-mm-dd hh:nn:ss”, vbMonday)= “2002-02-03 14:07:09”Format(DT, “yy-mmm-d at h:nn am/pm”)= “02-feb-3 at 2:07 pm”Format(DT, “dddd t\he y’t\h \da\y of yyyy”)= “Sunday the 34’th day of 2002”d^ Day of month, no leading zero “3”dd^ Day of month, two digits “03”ddd^ Day of week, short text “Sun”dddd^ Day of week, full text “Sunday”ww^ Week number. First day of week as 3rdparam, e.g. vbMonday.m^ Month, no leading zero “2”(Interpreted as minutes after h)mm^ Month, two digits “02”(Interpreted as minutes after h)mmm^ Month, short text “Feb”mmmm^ Month, full text “February”y^ Day of year “34”yy^ Year, two digits “02”yyyy^ Year, four digits “2002”h^ Hour, no leading zero “14” or “2”hh^ Hour, two digits “14” or “02”AM/PM^ Show AM or PM here, hours 12-basedam/pm^ Show am or pm here, hours 12-basedn^ Minutes, no leading zero “7”nn^ Minutes, two digits “07”s^ Seconds, no leading zero “9”ss^ Seconds, two digits “09” Named formats
Format function "Currency", "Short Date"...
