VBA Cheat Sheet, Cheat Sheet of MS Microsoft Excel skills

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..255 Boolean True (<> 0), False (=0) Integer 16 bit, -32,786 .. 32,767 Long 32 bit integer, -2.14E9 .. 2.14E9 Currency 64 bit integer / 10,000 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:00 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 lost ReDim Preserve d(5, 1 To 8) Last index range redefined, data preserved Erase d Releases memory for dynamic array Type Customer Simple modules only custID As Long custName As String * 50 custAddress As String End Type Dim custTable(20) As Customer Declarations i = i+2 ‘ Comment s = “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 Delete object if last ref Line continuation, comments, assignment If a=1 Then c=d+2 Single statement If a=1 Then c=d+2 . . . Multiple statements ElseIf a=2 Then c=d / 2 . . . Else c=0 . . . End If Select Case zip Case 4000 type = a . . . Case 4001, 5000 To 5999 type = b . . . Case Else type = c . . . End Select On 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 Let VBA handle errors Optional Optional Optional Conditional statements While a<10 May be empty loop c=c*2 . . . Exit not allowed Wend Do While a<10 May be empty loop c=c*2 . . . Exit Do Exit optional . . . Loop Do Loop at least once c=c*2 . . . Exit Do Exit optional . . . Loop While a<10 For i=1 To last Step 2 Step optional c=c*2 May be empty loop . . . Exit For Exit optional . . . Next i Don’t trust value of i when loop ends without Exit For 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 Sub Function fnc(a, b As t, Optional c, d) As String As String is optional If IsMissing(c) Then . . . fnc= result . . . Exit Function Exit optional . . . End Function Procedures = Subroutines and Functions 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 Sub Public Sub show(p) Visible to all modules Dim c Visible in this sub only . . . End Sub Module and Scope 23, -23, 0, -4.9E-20 Decimal numbers &h09A0FF, &o177 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 SQL True, False Booleans Date/time #10/24/02# 24th Oct 2002 #10/24/02 14:15:00# 24th Oct 02 at 14:15 #10/24/02 2:15 pm# 24th Oct 02 at 14:15 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!frmCst2 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 parameters Overflow or type mismatch for bad parameters. CByte(“37”) =37. Overflow outside 0..255 CInt(“2.6”) = 3 Round(2.6) = 3.0000 (Double) Rounding down: See Math functions Int, Fix. CLng(“99456”) = 99456 CCur(1/3) =0.3333 (always 4 decimals) CSng(“-2.6e-2”) = -0.026 CDbl(“-2.6”) = -2.6 CDbl(#12/31/1899#) = 1.0 CDate(“23-10-03”) = #10/23/2003# (as Double) Uses regional setting for input format CDate(1) = #12/31/1899# 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) = X As Variant. X may be Null Simple conversion functions 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 this ascii 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-4 LTrim(“ 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 is case 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” . . . Works just as well String functions 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) Error Iif and Choose 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) Upper bound for third index Array bounds 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 = 1 Mod 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) = "20:29" 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 integers And Logical And. Bit-wise And of integers Or Logical Or. Bit-wise Or of integers X Exclusive Or. Bitwise on integers Eqv Logical equivalence. Bitwise on integers Imp Logical implication. Bitwise on integers s Like “s?n” Wildcard compare. ? any char here. # any digit here. * any char sequence here . . . Operators, decreasing precedence VBA Reference Card