Vba Notes For Professionals Kicker-Books Download

VBA Notes for Professionals Kicker
15 Dec 2019 | 17 views | 0 downloads | 202 Pages | 2.25 MB

Share Pdf : Vba Notes For Professionals Kicker

Download and Preview : Vba Notes For Professionals Kicker


Report CopyRight/DMCA Form For : Vba Notes For Professionals Kicker



Transcription

Chapter 1 Getting started with VBA 2, Section 1 1 Accessing the Visual Basic Editor in Microsoft O ce 2. Section 1 2 Debugging 3,Section 1 3 First Module and Hello World 4. Chapter 2 Comments 6,Section 2 1 Apostrophe Comments 6. Section 2 2 REM Comments 6, Chapter 3 String Literals Escaping non printable characters and line continuations. Section 3 1 Escaping the character 7,Section 3 2 Assigning long string literals 7.
Section 3 3 Using VBA string constants 7,Chapter 4 VBA Option Keyword 9. Section 4 1 Option Explicit 9,Section 4 2 Option Base 0 1 10. Section 4 3 Option Compare Binary Text Database 12. Chapter 5 Declaring Variables 14,Section 5 1 Type Hints 14. Section 5 2 Variables 15,Section 5 3 Constants Const 18. Section 5 4 Declaring Fixed Length Strings 19,Section 5 5 When to use a Static variable 20.
Section 5 6 Implicit And Explicit Declaration 22,Section 5 7 Access Modi ers 22. Chapter 6 Declaring and assigning strings 24, Section 6 1 Assignment to and from a byte array 24. Section 6 2 Declare a string constant 24, Section 6 3 Declare a variable width string variable 24. Section 6 4 Declare and assign a xed width string 24. Section 6 5 Declare and assign a string array 24, Section 6 6 Assign speci c characters within a string using Mid statement 25. Chapter 7 Concatenating strings 26, Section 7 1 Concatenate an array of strings using the Join function 26.
Section 7 2 Concatenate strings using the operator 26. Chapter 8 Frequently used string manipulation 27, Section 8 1 String manipulation frequently used examples 27. Chapter 9 Substrings 29, Section 9 1 Use Left or Left to get the 3 left most characters in a string 29. Section 9 2 Use Right or Right to get the 3 right most characters in a string 29. Section 9 3 Use Mid or Mid to get speci c characters from within a string 29. Section 9 4 Use Trim to get a copy of the string without any leading or trailing spaces 29. Chapter 10 Searching within strings for the presence of substrings 30. Section 10 1 Use InStr to determine if a string contains a substring 30. Section 10 2 Use InStrRev to nd the position of the last instance of a substring 30. Section 10 3 Use InStr to nd the position of the rst instance of a substring 30. Chapter 11 Assigning strings with repeated characters 31. Section 11 1 Use the String function to assign a string with n repeated characters 31. Section 11 2 Use the String and Space functions to assign an n character string 31. Chapter 12 Measuring the length of strings 32, Section 12 1 Use the Len function to determine the number of characters in a string 32. Section 12 2 Use the LenB function to determine the number of bytes in a string 32. Section 12 3 Prefer If Len myString 0 Then over If myString Then 32. Chapter 13 Converting other types to strings 33, Section 13 1 Use CStr to convert a numeric type to a string 33. Section 13 2 Use Format to convert and format a numeric type as a string 33. Section 13 3 Use StrConv to convert a byte array of single byte characters to a string 33. Section 13 4 Implicitly convert a byte array of multi byte characters to a string 33. Chapter 14 Date Time Manipulation 34,Section 14 1 Calendar 34.
Section 14 2 Base functions 34,Section 14 3 Extraction functions 36. Section 14 4 Calculation functions 37,Section 14 5 Conversion and Creation 39. Chapter 15 Data Types and Limits 41,Section 15 1 Variant 41. Section 15 2 Boolean 42,Section 15 3 String 42,Section 15 4 Byte 43. Section 15 5 Currency 44,Section 15 6 Decimal 44,Section 15 7 Integer 44.
Section 15 8 Long 44,Section 15 9 Single 45,Section 15 10 Double 45. Section 15 11 Date 45,Section 15 12 LongLong 46,Section 15 13 LongPtr 46. Chapter 16 Naming Conventions 47,Section 16 1 Variable Names 47. Section 16 2 Procedure Names 50,Chapter 17 Data Structures 52. Section 17 1 Linked List 52,Section 17 2 Binary Tree 53.
Chapter 18 Arrays 54,Section 18 1 Multidimensional Arrays 54. Section 18 2 Dynamic Arrays Array Resizing and Dynamic Handling 59. Section 18 3 Jagged Arrays Arrays of Arrays 60,Section 18 4 Declaring an Array in VBA 63. Section 18 5 Use of Split to create an array from a string 64. Section 18 6 Iterating elements of an array 65, Chapter 19 Copying returning and passing arrays 67. Section 19 1 Passing Arrays to Proceedures 67,Section 19 2 Copying Arrays 67. Section 19 3 Returning Arrays from Functions 69,Chapter 20 Collections 71.
Section 20 1 Getting the Item Count of a Collection 71. Section 20 2 Determining if a Key or Item Exists in a Collection 71. Section 20 3 Adding Items to a Collection 72,Section 20 4 Removing Items From a Collection 73. Section 20 5 Retrieving Items From a Collection 74. Section 20 6 Clearing All Items From a Collection 75. Chapter 21 Operators 77,Section 21 1 Concatenation Operators 77. Section 21 2 Comparison Operators 77,Section 21 3 Bitwise Logical Operators 79. Section 21 4 Mathematical Operators 81,Chapter 22 Sorting 82. Section 22 1 Algorithm Implementation Quick Sort on a One Dimensional Array 82. Section 22 2 Using the Excel Library to Sort a One Dimensional Array 82. Chapter 23 Flow control structures 85,Section 23 1 For loop 85.
Section 23 2 Select Case 86,Section 23 3 For Each loop 87. Section 23 4 Do loop 88,Section 23 5 While loop 88. Chapter 24 Passing Arguments ByRef or ByVal 89, Section 24 1 Passing Simple Variables ByRef And ByVal 89. Section 24 2 ByRef 90,Section 24 3 ByVal 91,Chapter 25 Scripting FileSystemObject 93. Section 25 1 Retrieve only the path from a le path 93. Section 25 2 Retrieve just the extension from a le name 93. Section 25 3 Recursively enumerate folders and les 93. Section 25 4 Strip le extension from a le name 94, Section 25 5 Enumerate les in a directory using FileSystemObject 94.
Section 25 6 Creating a FileSystemObject 95, Section 25 7 Reading a text le using a FileSystemObject 95. Section 25 8 Creating a text le with FileSystemObject 96. Section 25 9 Using FSO BuildPath to build a Full Path from folder path and le name 96. Section 25 10 Writing to an existing le with FileSystemObject 97. Chapter 26 Working With Files and Directories Without Using FileSystemObject 98. Section 26 1 Determining If Folders and Files Exist 98. Section 26 2 Creating and Deleting File Folders 99. Chapter 27 Reading 2GB les in binary in VBA and File Hashes 100. Section 27 1 This have to be in a Class module examples later referred as Random 100. Section 27 2 Code for Calculating File Hash in a Standard module 103. Section 27 3 Calculating all Files Hash from a root Folder 105. Chapter 28 Creating a procedure 109,Section 28 1 Introduction to procedures 109. Section 28 2 Function With Examples 109,Chapter 29 Procedure Calls 111. Section 29 1 This is confusing Why not just always use parentheses 111. Section 29 2 Implicit Call Syntax 111,Section 29 3 Optional Arguments 112. Section 29 4 Explicit Call Syntax 112,Section 29 5 Return Values 113.
Chapter 30 Conditional Compilation 114, Section 30 1 Changing code behavior at compile time 114. Section 30 2 Using Declare Imports that work on all versions of O ce 115. Chapter 31 Object Oriented VBA 117,Section 31 1 Abstraction 117. Section 31 2 Encapsulation 117,Section 31 3 Polymorphism 121. Chapter 32 Creating a Custom Class 124,Section 32 1 Adding a Property to a Class 124. Section 32 2 Class module scope instancing and re use 125. Section 32 3 Adding Functionality to a Class 125,Chapter 33 Interfaces 127.
Section 33 1 Multiple Interfaces in One Class Flyable and Swimable 127. Section 33 2 Simple Interface Flyable 128,Chapter 34 Recursion 130. Section 34 1 Factorials 130,Section 34 2 Folder Recursion 130. Chapter 35 Events 132,Section 35 1 Sources and Handlers 132. Section 35 2 Passing data back to the event source 134. Chapter 36 Scripting Dictionary object 136,Section 36 1 Properties and Methods 136. Chapter 37 Working with ADO 138, Section 37 1 Making a connection to a data source 138.
Section 37 2 Creating parameterized commands 138,Section 37 3 Retrieving records with a query 139. Section 37 4 Executing non scalar functions 141,Chapter 38 Attributes 142. Section 38 1 VB PredeclaredId 142,Section 38 2 VB Var UserMemId 142. Section 38 3 VB Exposed 143,Section 38 4 VB Description 144. Section 38 5 VB Name 144,Section 38 6 VB GlobalNameSpace 144.
Section 38 7 VB Createable 145,Chapter 39 User Forms 146. Section 39 1 Best Practices 146,Section 39 2 Handling QueryClose 148. Chapter 40 CreateObject vs GetObject 150, Section 40 1 Demonstrating GetObject and CreateObject 150. Chapter 41 Non Latin Characters 151,Section 41 1 Non Latin Text in VBA Code 151. Section 41 2 Non Latin Identi ers and Language Coverage 152. Chapter 42 API Calls 153,Section 42 1 Mac APIs 153.
Section 42 2 Get total monitors and screen resolution 153. Section 42 3 FTP and Regional APIs 154,Section 42 4 API declaration and usage 157. Section 42 5 Windows API Dedicated Module 1 of 2 159. Section 42 6 Windows API Dedicated Module 2 of 2 163. Chapter 43 Automation or Using other applications Libraries 168. Section 43 1 VBScript Regular Expressions 168,Section 43 2 Scripting File System Object 169. Section 43 3 Scripting Dictionary object 169,Section 43 4 Internet Explorer Object 170. Chapter 44 Macro security and signing of VBA projects modules 173. Section 44 1 Create a valid digital self signed certi cate SELFCERT EXE 173. Chapter 45 VBA Run Time Errors 183,Section 45 1 Run time error 6 Over ow 183. Section 45 2 Run time error 9 Subscript out of range 183. Section 45 3 Run time error 13 Type mismatch 184, Section 45 4 Run time error 91 Object variable or With block variable not set 184.
Section 45 5 Run time error 20 Resume without error 185. Section 45 6 Run time error 3 Return without GoSub 186. Chapter 46 Error Handling 188,Section 46 1 Avoiding error conditions 188. Section 46 2 Custom Errors 188,Section 46 3 Resume keyword 189. Section 46 4 On Error statement 191,Credits 194,You may also like 196. Please feel free to share this PDF with anyone for free. latest version of this book can be downloaded from. https goalkicker com VBABook, This VBA Notes for Professionals book is compiled from Stack Over ow. Documentation the content is written by the beautiful people at Stack Over ow. Text content is released under Creative Commons BY SA see credits at the end. of this book whom contributed to the various chapters Images may be copyright. of their respective owners unless otherwise speci ed. This is an uno cial free book created for educational purposes and is not. a liated with o cial VBA group s or company s nor Stack Over ow All. trademarks and registered trademarks are the property of their respective. company owners, The information presented in this book is not guaranteed to be correct nor.
accurate use at your own risk, Please send feedback and corrections to web petercv com. GoalKicker com VBA Notes for Professionals 1,Chapter 1 Getting started with VBA. Version O ce Versions Release Date Notes Release Date. Vba6 2007 Sometime after 1 1992 06 30,Vba7 2010 2016 blog techkit com 2 2010 04 15. VBA for Mac 2004 2011 2016 2004 05 11, Section 1 1 Accessing the Visual Basic Editor in Microsoft. You can open the VB editor in any of the Microsoft O ce applications by pressing Alt F11 or going to the. Developer tab and clicking on the Visual Basic button If you don t see the Developer tab in the Ribbon check if. this is enabled, By default the Developer tab is disabled To enable the Developer tab go to File Options select Customize Ribbon.
in the list on the left In the right Customize the Ribbon treeview nd the Developer tree item and set the check. for the Developer checkbox to checked Click Ok to close the Options dialog. The Developer tab is now visible in the Ribbon on which you can click on Visual Basic to open the Visual Basic. Editor Alternatively you can click on View Code to directly view the code pane of the currently active element e g. WorkSheet Chart Shape,GoalKicker com VBA Notes for Professionals 2. You can use VBA to automate almost any action that can be performed interactively manually and also provide. functionality that is not available in Microsoft O ce VBA can create a document add text to it format it edit it and. save it all without human intervention,Section 1 2 Debugging. Debugging is a very powerful way to have a closer look and x incorrectly working or non working code. Run code step by step, First thing you need to do during debugging is to stop the code at speci c locations and then run it line by line to. see whether that happens what s expected, Breakpoint F9 Debug Toggle breakpoint You can add a breakpoint to any executed line e g not to. declarations when execution reaches that point it stops and gives control to user. You can also add the Stop keyword to a blank line to have the code stop at that location on runtime This is. useful if for example before declaration lines to which you can t add a breakpoint with F9. Step into F8 Debug Step into executes only one line of code if that s a call of a user de ned sub. function then that s executed line by line, Step over Shift F8 Debug Step over executes one line of code doesn t enter user de ned subs.
Step out Ctrl Shift F8 Debug Step out Exit current sub function run code until its end. GoalKicker com VBA Notes for Professionals 3, Run to cursor Ctrl F8 Debug Run to cursor run code until reaching the line with the cursor. You can use Debug Print to print lines to the Immediate Window at runtime You may also use Debug as a. shortcut for Debug Print,Watches window, Running code line by line is only the rst step we need to know more details and one tool for that is the watch. window View Watch window here you can see values of de ned expressions To add a variable to the watch. window either,Right click on it then select Add watch. Right click in watch window select Add watch,Go to Debug Add watch. When you add a new expression you can choose whether you just want to see it s value or also break code. execution when it s true or when its value changes. Immediate Window, The immediate window allows you to execute arbitrary code or print items by preceeding them with either the.
Print keyword or a single question mark,Some examples. ActiveSheet Name returns name of the active sheet, Print ActiveSheet Name returns the name of the active sheet. foo returns the value of foo,x 10 sets x to 10, Getting Setting values for variables via the Immediate Window can only be done during runtime. Debugging best practices, Whenever your code doesn t work as expected rst thing you should do is to read it again carefully looking for. If that doesn t help then start debugging it for short procedures it can be e cient to just execute it line by line for. longer ones you probably need to set breakpoints or breaks on watched expressions the goal here is to nd the. line not working as expected, Once you have the line which gives the incorrect result but the reason is not yet clear try to simplify expressions.
or replace variables with constants that can help understanding whether variables value are wrong. If you still can t solve it and ask for help, Include as small part of your code as possible for understanding of your problem. If the problem is not related to the value of variables then replace them by constants so instead of. Sheets a b c d 2 Range addressOfRange write Sheets 4 Range A2. Describe which line gives the wrong behaviour and what it is error wrong result. Section 1 3 First Module and Hello World, To start coding in the rst place you have to right click your VBA Project in the left list and add a new Module Your. rst Hello World Code could look like this,GoalKicker com VBA Notes for Professionals 4. Sub HelloWorld,MsgBox Hello World, To test it hit the Play Button in your Toolbar or simply hit the F5 key Congratulations You ve built your rst own. VBA Module,GoalKicker com VBA Notes for Professionals 5.
Chapter 2 Comments,Section 2 1 Apostrophe Comments. A comment is marked by an apostrophe and ignored when the code executes Comments help explain your. code to future readers including yourself, Since all lines starting with a comment are ignored they can also be used to prevent code from executing while. you debug or refactor Placing an apostrophe before your code turns it into a comment This is called. commenting out the line,Sub InlineDocumentation,Comments start with an. They can be place before a line of code which prevents the line from executing. Debug Print Hello World,They can also be placed after a statement. The statement still executes until the compiler arrives at the comment. Debug Print Hello World Prints a welcome message,Comments can have 0 indention.
or as much as needed,Comments can contain multiple apostrophes. Comments can span lines using line continuations,but this can make for hard to read code. If you need to have mult line comments it is often easier to. use an apostrophe on each line, The continued statement syntax is treated as part of the comment so. it is not possible to place an executable statement after a comment. This won t run Debug Print Hello World,Comments can appear inside or outside a procedure. Section 2 2 REM Comments,Sub RemComments, Rem Comments start with Rem VBA will change any alternate casing to Rem.
Rem is an abbreviation of Remark and similar to DOS syntax. Rem Is a legacy approach to adding comments and apostrophes should be preferred. Rem Comments CANNOT appear after a statement use the apostrophe syntax instead. Rem Unless they are preceded by the instruction separator token. Debug Print Hello World Rem prints a welcome message. Debug Print Hello World Prints a welcome message, Rem cannot be immediately followed by the following characters. Whereas the apostrophe syntax can be followed by any printable character. Rem Comments can appear inside or outside a procedure. GoalKicker com VBA Notes for Professionals 6,Chapter 3 String Literals Escaping non. printable characters and line,continuations,Section 3 1 Escaping the character. VBA syntax requires that a string literal appear within marks so when your string needs to contain quotation. marks you ll need to escape prepend the character with an extra so that VBA understands that you intend the. to be interpreted as a string,The following 2 lines produce the same output. Debug Print The man said Never use air quotes,Debug Print The man said Never use air quotes.
The man said Never use air quotes,The man said Never use air quotes. Section 3 2 Assigning long string literals, The VBA editor only allows 1023 characters per line but typically only the rst 100 150 characters are visible. without scrolling If you need to assign long string literals but you want to keep your code readable you ll need to. use line continuations and concatenation to assign your string. Debug Print Lorem ipsum dolor sit amet consectetur adipiscing elit. Integer hendrerit maximus arcu ut elementum odio varius. nec Integer ipsum enim iaculis et egestas ac condiment. um ut tellus, Lorem ipsum dolor sit amet consectetur adipiscing elit Integer hendrerit maximus arcu ut. elementum odio varius nec Integer ipsum enim iaculis et egestas ac condimentum ut tellus. VBA will let you use a limited number of line continuations the actual number varies by the length of each line. within the continued block so if you have very long strings you ll need to assign and re assign with concatenation. Dim loremIpsum As String,Assign the first part of the string. loremIpsum Lorem ipsum dolor sit amet consectetur adipiscing elit. Integer hendrerit maximus arcu ut elementum odio varius. Re assign with the previous value AND the next section of the string. loremIpsum loremIpsum, nec Integer ipsum enim iaculis et egestas ac condiment.
um ut tellus,Debug Print loremIpsum, Lorem ipsum dolor sit amet consectetur adipiscing elit Integer hendrerit maximus arcu ut. elementum odio varius nec Integer ipsum enim iaculis et egestas ac condimentum ut tellus. Section 3 3 Using VBA string constants, VBA de nes a number of string constants for special characters like. GoalKicker com VBA Notes for Professionals 7, vbCr Carriage Return Same as r in C style languages. vbLf Line Feed Same as n in C style languages, vbCrLf Carriage Return Line Feed a new line in Windows. vbTab Tab Character,vbNullString an empty string like.
You can use these constants with concatenation and other string functions to build string literals with special. characters,Debug Print Hello vbCrLf World,Debug Print vbTab Hello vbTab World. Hello World,Dim EmptyString As String,EmptyString vbNullString. Debug Print EmptyString, Using vbNullString is considered better practice than the equivalent value of due to di erences in how the. code is compiled Strings are accessed via a pointer to an allocated area of memory and the VBA compiler is smart. enough to use a null pointer to represent vbNullString The literal is allocated memory as if it were a String. typed Variant making the use of the constant much more e cient. Debug Print StrPtr vbNullString Prints 0,Debug Print StrPtr Prints a memory address. GoalKicker com VBA Notes for Professionals 8,Chapter 4 VBA Option Keyword.
Option Detail, Require variable declaration in the module it s speci ed in ideally all of them with this option. speci ed using an undeclared mispelled variable becomes a compilation error. Makes the module s string comparisons be case insensitive based on system locale. Compare Text,prioritizing alphabetical equivalency e g a A. Default string comparison mode Makes the module s string comparisons be case sensitive. Compare Binary comparing strings using the binary representation numeric value of each character e g. MS Access only Makes the module s string comparisons work the way they would in an SQL. Compare Database, Prevents the module s Public member from being accessed from outside of the project that. Private Module the module resides in e ectively hiding procedures from the host application i e not available. to use as macros or user de ned functions, Default setting Sets the implicit array lower bound to 0 in a module When an array is declared. Option Base 0, without an explicit lower boundary value 0 will be used.
Sets the implicit array lower bound to 1 in a module When an array is declared without an. Option Base 1,explicit lower boundary value 1 will be used. Section 4 1 Option Explicit, It is deemed best practice to always use Option Explicit in VBA as it forces the developer to declare all their. variables before use This has other bene ts too such as auto capitalization for declared variable names and. IntelliSense,Option Explicit,Sub OptionExplicit,Dim a As Integer. b 10 Causes compile error as b is not declared, Setting Require Variable Declaration within the VBE s Tools Options Editor property page will put the Option. Explicit statement at the top of each newly created code sheet.


Related Books

Design and Analysis of Slabs - Colin Caprani

Design and Analysis of Slabs Colin Caprani

Civil Engineering Design (1) ... Ultimate Behavior of One-Way Spanning Slab ... do a punching shear calculation based on the reaction but, in a slab bridge, a

PERFORMANCE BASED SEISMIC DESIGN OF RCC BUILDING - irjet.net

PERFORMANCE BASED SEISMIC DESIGN OF RCC BUILDING irjet net

PERFORMANCE BASED SEISMIC DESIGN OF RCC BUILDING ... 2002, IS 456: 2000) for zone 5, 4 and 3 for Maximum Considered Earthquake ... Slab thickness is

S. SUKHTHANKER - songs-from-goa.at

S SUKHTHANKER songs from goa at

to read Marathi folk-songs published, though sporadically, by Rajawade and Mundale, historians of fame, m the quarterly published by their Historical Association and the Bengali folk-songs rendered into English in the Calcutta Review which made my interest in Goan folk-poetry purposeful . .. I began to

IGCSE English as a Second Language Third edition

IGCSE English as a Second Language Third edition

English as a Second Language Third edition ... necessary for success in all the papers of the IGCSE E2L exam. Workbook 2 provides an additional source

Hidup Baru Dalam Kristus - members.tjc.org

Hidup Baru Dalam Kristus members tjc org

Hari ini, kita pun harus dapat memahami pengajaran ini. Sebagai umat Kristen, kita memiliki pengharapan untuk dapat memasuki tanah perjanjian kita. Injil yang telah Saudara dengar dan terima adalah kabar baik mengenai tanah perjanjian ini, yang telah diberitakan Yesus Kristus kira-kira 2000 tahun yang lalu. Tetapi agar

Ayat-ayat ALKITAB untuk Kehidupan Sehari-hari

Ayat ayat ALKITAB untuk Kehidupan Sehari hari

siang dan malam , supaya engkau ber-tindak hati -hati sesuai dengan segala yang tertulis di dalamnya, sebab dengan demikian perjalananmu akan berhasil dan engkau akan beruntung . (Yos. 1:8) Firman-Mu itu pelita bagi kakiku dan terang bagi jalanku . (Mzm. 119:105)

Guide To Public Sector Labor Relations Law in Michigan

Guide To Public Sector Labor Relations Law in Michigan

Guide to Public Sector Labor. Relations Law in Michigan . Law and Procedure before the . Michigan Employment Relations Commission. Dedicated to the memory of Hyman Parker

Graduate University for Advanced Studies

Graduate University for Advanced Studies

advances in heavy tailed risk modeling: a handbook of operational risk online books mathematics & statistics 9780470517253 advances in statistical monitoring of complex multivariate processes - with applications in industrial process control online books: mathematics & statistics 9781118945520

Analytics in Healthcare - pearsoncmg.com

Analytics in Healthcare pearsoncmg com

Analytics in Healthcare and the Life Sciences Strategies, Implementation Methods, and Best Practices Edited by Dwight McNeill Foreword by Thomas H. Davenport

Elementary Library Books - Notepad

Elementary Library Books Notepad

Elementary Library Books Benton Bibliography Report 5/25/2007 @ 7:33am Page 1------------------------------------------------------------------------------031.02 Wor ...