Access VBA Fundamentals - Access All In One
Access VBA Made Easy Access VBA Fundamentals Level 5 www.AccessAllInOne.com
This guide was prepared for AccessAllInOne.com by: Robert Austin This is one of a series of guides pertaining to the use of Microsoft Access. AXLSolutions 2012 All rights reserved. No part of this work may be reproduced in any form, or by any means, without permission in writing. 1 Page
Contents 09 - Loops . 4 A Word of Warning – Infinite Loops . 4 For Next . 4 For i 1 to 10 : {code block} : Next . 4 For Step Next . 6 Use Step to Count Backwards . 6 Using Dynamic startValue, endValue and stepValues . 6 For Each . 7 Demonstrate with Collections . 7 Demonstrate with Arrays . 8 Exit For . 8 While Wend . 9 While (false) . 9 Exit While . 10 Loop/Do Until/While . 11 Nesting Loops . 12 Nested Loops and Multidimensional Arrays . 13 A Useful Implementation of Nested Loops . 14 DoEvents . 14 Questions . 18 10 - Recordsets . 21 Introduction: What are Recordsets . 21 Checking DAO is Referenced. 21 Checking ADO is Referenced. 22 Adding Missing DAO and ADO References . 22 Adding DAO References . 22 Adding ADO References . 23 Again, Check DAO and ADO References . 23 Declaring a Recordset Object . 24 DAO Recordsets . 24 ADODB Recordsets . 24 DAO vs ADODB . 25 What’s the Difference? . 25 2 Page
Getting Recordsets with CurrentDb (DAO) . 26 Getting Recordsets with CurrentProject (ADODB) . 26 Cursors . 26 Locks . 27 Recordsets and SQL. 29 Opening a Recordset. 29 Counting Records . 29 Looping Through a Recordset .30 While and Until Loops .30 For Each . 31 Backwards Through the Records . 32 Last and First . 32 Editing Records (and the With Keyword) . 33 Deleting Records. 34 Adding Records . 34 Closing Recordsets. 35 Common Errors . 36 Using Form.Recordsets . 36 Using ADO in Business Logic Libraries . 36 Removing Recordset Objects. 36 Record Locking . 36 Assuming Recordset Operations Will Work . 36 Dirty Reads and Buried Updates . 36 Batch Updating . 36 SQL is Better than Recordsets . Error! Bookmark not defined. Questions . 37 Answers - Loops. 39 Answers - Recordsets. 42 3 Page
09 - Loops After conditionals and arrays, loops form the next major component in VBA. A loop is a block of code that executes again and again until either an expression equates to false or is broken by way of an Exit statement. What makes loops useful is that they can work with arrays and collections, they can perform tasks over and over until a condition is met and they can perform calculations over and over until you force them to stop. There are several ways to express this need to loop and VBA isn’t short on constructs for doing it. So we will get straight into a For loop, but first A Word of Warning – Infinite Loops If you get stuck in an infinite loop or the loop is taking a lot longer than you expected, use Break to stop VBA from executing. On most keyboards this is a secondary function to the Delete key. For Next A For loop goes around and around incrementing some variable counter by a figure you determine (the default is 1). It executes a code block between the keywords For and Next until some condition with the variable is met. For i 1 to 10 : {code block} : Next Let’s get straight into the code and see what a For loop does. 1 2 3 4 5 6 7 Sub forLoop1() Dim i As Integer For i 1 To 10 Debug.Print i Next i End Sub forLoop1 1 2 3 4 5 6 7 8 9 10 Figure 9.1 The code block contains a Debug.Print statement which prints the value of i. The For statement increments i by 1 on each iteration and stops when i gets to 10. “i” is a variable just like any other and we can change it and the For loop will follow our alterations. 4 Page
1 2 3 4 5 6 7 8 Function forLoop2() Dim i As Integer For i 1 To 10 Debug.Print i i i 1 Next i Function forLoop3() Dim i As Integer For i 10 To 1 Debug.Print i Next i End Function End Function forLoop2 1 3 5 7 9 ‘ nothing outputted Figure 9.2 Here forLoop2 executes the code block which adds I 1 to counter “i” and the loop doesn’t mind! It just does its job and breaks when i 10. By default For increments forwards by the value 1 so in forLoop3 “i” is already at its maximum value and the for loop immediately exits without executing the code block. We don’t have to rely on the For counter in the code block; we can use another counter to keep track of the iterations. This is demonstrated in forLoop4. In forLoop4 counter “t” is incremented by 3 on each loop. You will notice that “t” wasn’t set at the start but VBA always sets Integer variables to 0 on initialisation. 1 2 3 4 5 6 7 8 9 Sub forLoop4() Dim i As Integer, t As Integer For i 1 To 10 Debug.Print t t t 3 Next i Sub forLoop5() Dim i As Integer, t As Integer For i 1 To 5 5 Debug.Print i Next i End Sub End Sub forLoop2 0 3 6 9 12 15 18 21 24 27 forLoop5 1 2 3 4 5 6 7 8 9 10 Figure 9.3 In forLoop5 we demonstrate that the end value of the For loop (5 5) can be an expression. We will be looking at this in greater detail later in this unit. 5 Page
For Step Next In forLoop2 we adjusted the counter “i” to increment by an additional 1 for each loop. We can do the same by using the Step option part of the For loop Step tells For to increment its counter by a value other than the default value of 1. 1 2 3 4 5 6 7 Sub forLoop2b() Dim i As Integer For i 1 To 10 Step 2 Debug.Print i Next i Sub forLoop3b() Dim i As Integer For i 10 To 1 Step -1 Debug.Print i Next i End Sub forLoop6 1 3 5 7 9 End Sub forLoop3b 10 9 8 7 6 5 4 3 2 1 Figure 9.4 forLoop2b has been altered to produce the same output as forLoop2 but without using the additional variable “t”. Use Step to Count Backwards forLoop3b is the same as forLoop3 EXCEPT Step -1, and it works! Step tells the For loop to increment it’s counter by the value after Step. In this case the value of Step is set to -1 so the counter counts backwards by 1 on every iteration. Using Dynamic startValue, endValue and stepValues The startValue and endValue and stepValue are all expressions, so as long as the expressions evaluate to a number For will accept them. Here we start at 4, step by 3 and finish at 16. 1 2 3 4 5 6 7 8 9 Sub forLoop6() Dim startValue As Integer, endValue As Integer, stepValue As Integer startValue 4: endValue 16: stepValue 3 For i startValue To endValue Step stepValue Debug.Print i Next i End Sub forLoop6 4 7 10 13 16 Figure 9.5 6 Page
For Each The previous examples were just to get you understanding what a For loop does which also illustrates what all loops do. Now we’ll do something useful with the loop and see it in action. Below is a regular For loop that iterates over the AllForms collection printing out the form names in the immediate window. 1 2 3 4 5 6 7 Sub forLoop7() For t 0 To CurrentProject.AllForms.Count - 1 Debug.Print CurrentProject.AllForms(t).Name Next End Sub Form1 frmEvents frmTimer frmHomeTest frmStudentsDataEntry Figure 9.6 Using Loops with Collections Alternatively, the For Each loop explicitly loops over each element in the collection AllForms. 1 2 3 4 5 6 7 8 Sub forLoop8() Dim acObject As AccessObject For Each acObject In CurrentProject.AllForms Debug.Print acObject.Name Next End Sub Form1 frmEvents frmTimer frmHomeTest frmStudentsDataEntry Figure 9.7 One thing to note; each object should be declared as the same type as the objects held within the collection, or of type Variant. The only problem with using Variant is that it takes longer to execute and work with, so where possible, or known, declare the object variable correctly. 7 Page
Demonstrate with Arrays For Each also works with standard arrays. 1 2 3 4 5 6 7 8 9 Sub forLoop9() Dim myArray() As Variant, element As Variant myArray Array("hello", "world!", "merry", "christmas", 2012) For Each element In myArray Debug.Print element Next End Sub forLoop9 hello world! merry christmas 2012 Figure 9.8 forLoop9 uses a Variant Array to store Strings and Integers. To keep it simple “element” has also been declared as a Variant allowing VBA to automatically set “element” to whatever position x is within myArray. Exit For To leave the For or For Each loop before their natural end we can use the Exit For statement. 1 2 3 4 5 6 7 8 9 10 11 Sub forLoop10() Dim myArray() As Variant, element As Variant myArray Array("hello", "world!", "merry", "christmas", 2012) For Each element In myArray Debug.Print element If element "Merry" Then Exit For Next End Sub forLoop10 hello world! Merry Figure 9.9 The conditional checks the value of the present element in the array and exits when the string “Merry”. 8 Page
While Wend A While loop executes its code blocks over and over until its expression is not True. The following is an infinite loop, so use your Break key to stop the execution. 1 2 3 4 5 Sub whileLoop1() While (True) Debug.Print "Hello World!" Wend End Sub whileLoop1 Hello World! Hello World! Hello World! Hello World! Hello World! . Figure 9.10 That isn’t much use, but we can change it to read input from the user until an empty string is received. 1 2 3 4 5 6 7 8 Sub whileLoop2() Dim str As String str InputBox("Enter some text") While (str "") Debug.Print "You wrote: " & str str InputBox("Enter some text") Wend End Sub whileLoop2 You wrote: test 1 You wrote: test 2! You wrote: test 3? Figure 9.11 While (false) The While statement only executes its code block if the expression in parenthesis is equal to True. 1 2 3 4 5 Sub whileLoop3() While (False) MsgBox "I was not called!" Wend End Sub whileLoop3 ‘ nothing is executed! Figure 9.12 While is often used to cycle through Recordsets and Files. We can use each object’s EOF (End-Of-File) property as the expression to the While statement. 9 Page
1 2 3 4 5 6 7 8 9 10 Sub whileLoop4() Dim rs As DAO.Recordset rs getRecordSet() ' this method is for illustrative purposes only While (Not rs.EOF) Debug.Print rs!FieldName rs.MoveNext ' object rs told to move to the next row Wend End Sub whileLoop4 ‘getRecordSet() is for illustrative purposes only {FieldName of each row in Recordset} Figure 9.13 EOF is set to True when the Recordset object reaches the last element and attempts move forward once more, so this loop cycles over the Recordset object and prints the value of field FileName. Note We will be reviewing recordsets in detail in the next unit. Exit While To exit a while loop isn’t as trivial a task as with other loop structures. To exit a While one must force the While expression to be false. 1 2 3 4 5 6 7 8 9 10 11 Sub whileLoop5() Dim rs As DAO.Recordset, exitMe As Integer Set rs CurrentDb.OpenRecordset("SELECT * from tblStudents") While (Not rs.EOF And exitMe 5) Debug.Print exitMe; rs!LastName rs.MoveNext ' object rs told to move to the next row exitMe exitMe 1 Wend End Sub whileloop5 0 Bedecs 1 Gratacos Solsona 2 Axen 3 Lee 4 O’Donnell Figure 9.14 The variable exitMe is incremented by 1 over each loop and forces the expression in the While to be false after the 5th iteration 10 P a g e
Loop/Do Until/While Another set of statements perform like a While loop and permit exiting the loop at any point without changing the statement’s expression. 1 2 3 4 5 6 7 8 9 10 11 Sub doWhile1() Dim kitchenItems() As Variant, i As Long kitchenItems Array("Cooker", "Fridge", "Cutlery", "Crockery", "Dishwasher", "Table and Chairs") Do While (i UBound(kitchenItems) 1) Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i) i i 1 Loop End Sub doWhile1 Item 0 is Item 1 is Item 2 is Item 3 is Item 4 is Item 5 is Cooker Fridge Cuttlery Crockery Dishwasher Table and Chairs doWhile2 below performs the same operation as doWhile1 above except it uses Exit Do to finish the loop. 1 2 3 4 5 6 7 8 9 10 11 12 Sub doWhile2() Dim kitchenItems() As Variant, i As Long kitchenItems Array("Cooker", "Fridge", "Cutlery", "Crockery", "Dishwasher", "Table and Chairs") Do While (True) Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i) i i 1 If i UBound(kitchenItems) 1 Then Exit Do Loop End Sub Figure 9.15 Do Until executes its code block while its expression is False, this is the opposite of the Do While loop. 1 2 3 4 5 6 7 8 9 10 11 12 Sub doUntil1() Dim kitchenItems() As Variant, i As Long kitchenItems Array("Cooker", "Fridge", "Cutlery", "Crockery", "Dishwasher", "Table and Chairs") Do Until (False) Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i) i i 1 If i UBound(kitchenItems) 1 Then Exit Do Loop End Sub 11 P a g e
doUntil1 Item 0 is Item 1 is Item 2 is Item 3 is Item 4 is Item 5 is Cooker Fridge Cuttlery Crockery Dishwasher Table and Chairs Figure 9.16 Finally, the Do Loop statement is identical to the While statement; neither has a clause to allow the loop to exit, but do allow the keyword Exit. Sticking with the Kitchen Items example: 1 2 3 4 5 6 7 8 9 10 11 12 Sub doLoop1() Dim kitchenItems() As Variant, i As Long kitchenItems Array("Cooker", "Fridge", "Cutlery", "Crockery", "Dishwasher", "Table and Chairs") Do Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i) i i 1 If i UBound(kitchenItems) 1 Then Exit Do Loop End Sub doloop1 Item 0 is Item 1 is Item 2 is Item 3 is Item 4 is Item 5 is Cooker Fridge Cutlery Crockery Dishwasher Table and Chairs Figure 9.17 Nesting Loops A loop inside a loop is termed a nested loop. We’ll make a grid of numbers to illustrate. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sub nestedLoop1() Dim y As Integer, x As Integer Dim xString As String For y 0 To 9 For x 0 To 9 xString xString CStr(x) " " Next x Debug.Print " xString "" line " CStr(y) " - " xString Next y End Sub nestedLoop1 line 0 - 0 1 2 3 4 5 6 7 8 9 line 1 - 0 1 2 3 4 5 6 7 8 9 12 P a g e
line line line line line line line line 2 3 4 5 6 7 8 9 - 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 5 5 5 5 5 5 5 5 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 Figure 9.18 The inner loop populates xString with numbers, 0 to 9. The outer loop prints “ line “ and the value of y concatenated with xString. xString is then cleared and y iterates to the next y value. Nested Loops and Multidimensional Arrays 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Function nestedLoop2() Dim y As Integer, x As Integer Dim twoDArray(10, 10) As String, xString As String For y 0 To 9 For x 0 To 9 twoDArray(y, x) y * x Next x Next y For y 0 To 9 For x 0 To 9 xString xString (Right("00" & CStr(twoDArray(y, x)), 3)) " " Next x Debug.Print " Line " & CStr(y) & " - " & xString xString "" Next y End Function nestedLoop2 Line 0 - 000 Line 1 - 000 Line 2 - 000 Line 3 - 000 Line 4 - 000 Line 5 - 000 Line 6 - 000 Line 7 - 000 Line 8 - 000 Line 9 - 000 000 001 002 003 004 005 006 007 008 009 000 002 004 006 008 010 012 014 016 018 000 003 006 009 012 015 018 021 024 027 000 004 008 012 016 020 024 028 032 036 000 005 010 015 020 025 030 035 040 045 000 006 012 018 024 030 036 042 048 054 000 007 014 021 028 035 042 049 056 063 Figure 9.19 13 P a g e 000 008 016 024 032 040 048 056 064 072 000 009 018 027 036 045 054 063 072 081
A Useful Implementation of Nested Loops A more practical example is to iterate over a Collection within a Recordset. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Sub nestedLoop3() On Error Resume Next Dim rs As DAO.Recordset, field As DAO.field Dim rowText As String Set rs CurrentDb.OpenRecordset("SELECT * FROM tblStudents") While (Not rs.EOF) For Each field In rs.Fields rowText rowText & field.Name & " " & CStr(field) & ", " Next Debug.Print rowText rowText "" rs.MoveNext Wend End Sub nestedLoop3 StudentID 1, LastName Bedecs, FirstName Anna ‘ more commented out StudentID 2, LastName Gratacos Solsona, FirstName Antonio ‘ StudentID 3, LastName Axen, FirstName Thomas, ‘ Figure 9.20 Here the Fields collection is being iterated over and rowText populated with the field’s name and value. Note The On Error statement forces VBA to skip any error messages and Resume Execution. DoEvents Arrays and Collections are mainly resident in memory but don’t drain on CPU power after they have been set. Loops however are resident in the CPU and occupy it as much as it needs even at the expense of other loops and operations. This can lead to problems for other processes that fight for limited CPU resources and if the operating system does not implicitly implement multitasking, loops can cause a system to appear to hang until they finish. This is also a problem within your own application. You may have created a progress bar of some sort but that bar never updates; your loop is so resource intensive it doesn’t allow your application to do anything until it finishes. You can however willingly relinquish the CPU by inserting the DoEvents command. DoEvents pause the current loop and allow other functions that have requested CPU time to execute; this includes your progress bar. Your loop will get back control of the CPU once all other CPU bound tasks have at least performed some of their actions (e.g. they may also implement DoEvents whilst they are executing a loop which gives you loop time a little later). We will illustrate this by using a CPU intensive loop without DoEvents CPUTask1(), and a CPU intensive loop with DoEvents CPUTask2(). Execute each task in turn and try to navigate around Access (restore or switch to Access, open a menu or move a window). 14 P a g e
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Sub CPUTask1() Dim t As Double, zzz As Single Debug.Print "CPUTask1 Start Now() " & Now() For t 1 To 100000000 zzz zzz (t / 2) If (t Mod 10000000) 0 Then Debug.Print t Next Debug.Print "CPUTask1 End Now() " & Now() End Sub Sub CPUTask2() Dim t As Double, zzz As Single Debug.Print "CPUTask2 Start Now() " & Now() For t 1 To 100000000 zzz zzz (t / 2) If (t Mod 10000000) 0 Then DoEvents Debug.Print t End If Next Debug.Print "CPUTask2 End Now() " & Now() End Sub CPUTask1 CPUTask1 Start Now() 25/12/2012 14:51:03 10000000 20000000 30000000 40000000 50000000 60000000 70000000 80000000 90000000 100000000 CPUTask1 End Now() 25/12/2012 14:51:14 CPUTask2 CPUTask2 Start Now() 25/12/2012 14:52:07 10000000 20000000 30000000 40000000 50000000 60000000 70000000 80000000 90000000 100000000 CPUTask2 End Now() 25/12/2012 14:52:18 Figure 9.21 During CPUTask1’s execution it will not be possible to move anything and any updates to the Access application are queued until the function is complete. You may even find that other applications do not function at all or only a little whilst the function is executing 15 P a g e
During CPUTest2’s execution the DoEvents statement is fired about every second and allows the Access application enough time to perform some functions, like repainting a window or opening a menu. You may find that other applications may act the same whilst they also queue up their window requests and wait for your function to call DoEvents. But if you are using Windows 7 or other multitasking operating systems, other programs should not be affected. The point to note here is that loops can and do use up CPU resources and just as one has to be vigilant with releasing memory one also has to be vigilant not to monopolise said CPU resources. 16 P a g e
17 P a g e
Questions 1) True or False a. A loop is a circular object instantiated by ReDim’ing an object reference. b. For and Step are part of the For statement. c. Next denotes the end of a For code block. d. An infinite loop is magic. e. While used with Step is valid. 2) What is the output of the following code 1 2 3 4 5 6 7 Function forLoop2() Dim i As Integer For i 1 To 50 Step 10 Debug.Print “i ” & cstr(i) Next i End Function 3) Change the following code to print hello world ten times using i as your counter 1 2 3 4 5 Function whileLoop1() Dim a As Boolean, i As Integer While (Not a) Debug.Print "Hello World!" Wend End Function 4) Which of the following pieces of code are infinite loops (a) While(true) Debug.print 1 Wend (b) Do while(true) Exit Do Loop (c) Do Until(false) ‘Exit Loop (d) For I 1 to 10 I I -1 Next (e) A 1 Loop While (A 1) Loop (f) A 3 While(A 0) A A-1 : Wend 5) When iterating over a collection, which loop structures would you use? 6) Which of the following are multi-dimensional arrays a. A Array(10,5) b. Dim myString(20) As String 18 P a g e
c. B(50,50) d. Dim (9,9)myVar as Integer 7) Which of the following are characteristic of DoEvent? a. Allows non-multi-tasking OS to “multi-task” b. Schedules a future event c. Allows Access forms to repaint d. Used in loops to relinquish CPU resources e. Reserves memory for an array 8) Write a For loop that prints out the following array: carParts eel”,”Wishbone”,”Sump”) 9) Write a While loop that loops 100 times printing to the immediate window every second iteration. 10) Write a For Each loop that iterates over the CurrentProject.AllMacros collection and prints their names to the immediate window. 11) Using the following arrays, complete the questions that follow aa array(10,6,20,99) bb array(1,2,3,4) cc array(aa,bb) aa(0) bb(3) cc(0)(0) cc(1)(0) bb(4) cc(1)(3) bb(8-aa(1)) aa(0) bb(3) aa(bb(0)) cc(0)(2) a. Could the above array be iterated using loops? b. Which loops would be most suitable and why? 12) Using a Integer array called “IDs” with 10 elements, populate the array with numbers 1 to 10 . 13) How many “Running!” lines are printed to the immediate window? 1 2 3 4 5 Function runningLoop() While (false) Debug.print “Running!” Wend End Function 14) When does the following loop exit? 1 2 3 4 5 Function exitAtFive() Dim a as Integer : a 100 While (a 5) a a - 1 Wend End Function 19 P a g e
15) What is the result of the following: a. Dim a1(20) : UBound(a1) ? b. Dim b(10) : LBound(b10) ? c. Dim c As New Collection: c.Add "Hi": c.Add "#12/12/2010#": c.Count ? 16) Examine the following function newChessboard() 1 2 3 4 5 Function newChessboard() Dim chessboard(8), pieces1, pieces2, places, none As String pieces1 Array("rook", "knight", "bishop", "king", "queen", "bishop", "knight", "rook") pieces2 Array("pawn", "pawn", "pawn", "pawn", "pawn", "pawn", "pawn", "pawn") none "empty" places Array(none, none, none, none, none, none, none, none) chessboard(0) pieces1 chessboard(1) pieces2 chessboard(2) places chessboard(3) places chessboard(4) places chessboard(5) places chessboard(6) pieces2 chessboard(7) pieces1 newChessboard chessboard End Function a. Describe the output of the function 17) What is the difference between chessboard(8,8) and newChessboard in the above function? a. What is the purpose of the array pieces1 18) Write a loop that prints out chessboard(7) a. And, write a loop that prints out column 1 of the chessboard 19) Write a loop that prints only the positions “(x)(y) {content}” of squares that are not “empty” hint: you will need to use If, Loops and arrays 20) What happens if we ask what is in element chessboard(9)(2)? 20 P a g e
10 - Recordsets Introduction: What are Recordsets Strictly speaking a Recordset is an object available to VBA and Access that encapsulates the functionality and code necessary to interact with the Jet Database Engine and any other data source available via ODBC. In simple terms it lets you play with data held in tables in Access. You already know there are tables that contain data in Access, but did you know that VBA cannot directly access these tables? Tables are stored in an arcane fashion quite unlike anything in VBA and we need assistance from the Recordsets object to gloss over the issues surrounding accessing the data. Recordsets act like a cursor, providing VBA with a neat interface and a number of utility functions and properties that we will need in order to work with the data (column names, data types, record count etc.) Importantly, Recordsets allow us to handle data record by record (which is the only way VBA can deal with data); it is also the only way Access can deal with data and it too relies on the Recordset object to populate forms and reports. In this unit we will learn how to declare the two types of Recordset object – DAO and ADODB – and use them to manipulate the data in the TeachingInstituteSoftwareSystem.accdb database. To use this tutorial you should be working in the TeachingInstituteSoftwareSystem.accdb database and be familiar with adding new Modules and editing Form Modules. A familiarity with the database tables would also be an advantage to help you visualise what is a
There are several ways to express this need to loop and VBA isn't short on constructs for doing it. So we will get straight into a For loop, but first A Word of Warning - Infinite Loops If you get stuck in an infinite loop or the loop is taking a lot longer than you expected, use Break to stop VBA from executing.
Updated to include preliminary information on the VBA language from the pre-release version of VBA 7. 3/15/2010 1.0 Major Updated to include information on the VBA language as of VBA 7. 3/15/2012 1.01 Major Updated to include information on the VBA language as of VBA
13.2. Excel and VBA Implementation 248 APPENDIX A VBA Programming 255 A.1 Introduction 255 A.2 A Brief History of VBA 255 A.3 Essential Excel Elements for VBA 256 A.3.1 Excel Cell Reference 257 A.3.2 Excel Defined Names 261 A.3.3 Excel Worksheet Functions 264 A.4 The VBA Development Enviro
begin using VBA and writing a simple macro. You access VBA through Inventor using the Macro Visual Basic Editor command in the Tools menu, or by pressing Alt-F11. Once the VBA environment is open, the first thing I recommend you do is change some of the VBA settings. In the VBA environment run the Options command from the Tools menu. Change the
We can use VBA in all office versions right from MS-Office 97 to MS-Office 2013 and also with any of the latest versions available. Among VBA, Excel VBA is the most popular one and the reason for using VBA is that we can build very powerful tools in MS Excel using linear programming. Application of VBA
Programming: VBA in MS Office – An Introduction 3 IT Learning Programme 1.4. What is VBA? VBA is a high-level programming language that sits behind the Microsoft Office suite of applications. It is made available, through the built-in VBA Editor in each applicable application, to the end user to create code that can be executed within
VBA4-Using Cell Formulas in VBA Page 3 of 7 O:\COURSES\VBA\Online VBA\VBA4-Using Cell Formulas in VBA.docx 8. While ActiveCell.Value "" is the way to loop as long as there is a value in the current cell. You also might use an offset: While ActiveCell.Offset(0,-1).Value "" will test the column to the left of the current column.
To begin, start with a NEW spreadsheet and access VBA. In order to access RIT‟s built-in VBA commands, you will need to add it as a reference to your VBA project by going to: Tools - References When the Reference window appears, scroll down and check the item "Rotman Interactive Trader". This step loads the Rotman commands and functions .
2 "ONE-CLICK VALUE TREND" VBA SCRIPT INTRODUCTION The "One-Click Value Trend" VBA script places a displayed value into a PI ProcessBook trend when it is selected. You can access a trend of any value in your display.This VBA script references a Trend object named PointTr
