Guía práctica para crear y utilizar macros en Excel con VBA, Apuntes de Informática

¡Descarga Guía práctica para crear y utilizar macros en Excel con VBA y más Apuntes en PDF de Informática solo en Docsity! ©Note: Copyright 2018, XelPlus.com, Leila Gharani, MA UNLOCK EXCEL VBA & MACROS With Leila Gharani (Microsoft Excel MVP & Udemy bestselling Instructor) 1 Getting Started 1 Download the Project Guide PDF File. This gives you an overview of the projects we cover inside the course. 2 Download the Excel files and practice along. Don’t miss the quizzes and the activities at the end of the sections. 4 Make sure you save your files as macro-enabled files and activate macros when looking at the completed versions of the Workbooks. 3 Activate the developer tab in Excel by going to Files / Options / Customize Ribbon and put a tick-mark beside Developer. The Workbooks & tools are created for Excel on Windows only. Please close all other Workbooks when working with the course files.5 2 2 1 3 Become familiar with Macros, VBE & the Object Model How to Reference Ranges, Worksheets & Workbooks Variables, Data Types Working with Collections & Making Decisions Useful VBA Functions Debugging code, Handling Errors & Procedure Scope Interacting with Other Applications (Word, PowerPoint, Outlook, PDF) Worksheet & Workbook Events ActiveX Form Controls & VBA UserForms Looping in VBA to Control the Flow of Code Working with Arrays Working with Files, Folders & Text Files Excel Tables, Formulas & Pivot Tables Function Procedures & Working with Charts More… 5 Your First Macro Recording Become familiar with Macros & Visual Basic Editor # How to Record a Macro # Where your Macro is Recorded # Record Practical Macros # How to Run Macros 6 How to Record a Macro 1 Do a test run before you press record 2 a) Click on the record macro button at the bottom left-hand side b) Use shortcut key Alt + T + M + R c) Go to Developer tab – Record macro d) Go to View tab – Record macro 4 Run through the steps the macro needs to do 3 Name your Macro, assign a shortcut key & decide where to store it (workbook or personal macro workbook) 5 Stop the macro 7 Change in Comments to Notes In Office 365 “comments” have been renamed to “notes”. VBA syntax has not changed except we have new syntax for the new comments feature. Note: If you have Office 365 and you are following along with the lectures, when you see a comment being added to Excel, please add a “note”. VBA syntax has remained unchanged. The new comments are called threaded comments because you can reply to them. They have a new VBA syntax called CommentThreaded. Working with comments & notes 10 The Object Model Visual Basic Essential: Understanding the “dot” in code # Color Guidelines & Keyboard Shortcuts # What the Object Model is # Object Properties & Methods # How to find the Correct Syntax (property or method) 11 Visual Basic for Applications (VBA) # Writing VBA Code (Macros) = Manipulating objects on the screen # These objects have code words so you can easily refer to them Excel VBA code, generally uses a mix of VBA & Excel Object libraries # These code words are kept in the reference library To control other applications from Excel, a good option is to activate the reference to that application’s object library by going to Tools / References Working with VBA (Macro) code 12 The Object Model # VBA is Object Oriented. Before you perform any actions you need to specify what object to perform on # VBA Syntax is Object First - then action # Objects can have relationships to other objects This relationship is called the object hierarchy Home District City The City “Object” Includes a collection of Districts District “Object” Includes a collection of Houses Home “Object” is a part of the District collection 15 Excel’s Object Model Application Application.Workbooks(“Name”).Worksheets(“WSName”).Range(“A1”) Examples Workbooks Worksheets (Workbook) (Worksheet) Range Application.ThisWorkbook.Worksheets(“WSName”).Range(“A1”) Worksheets(“WSName”).Range(“A1”) assumes active workbook Range(“A1”) assumes active worksheet ActiveCell assumes the cell that is marked in the active worksheet 16 Properties in VBA Shoe.Gender = Male Shoe.Heel.Material = ? 1. Color 2. Size 3. Type 4. Engine 5. Age 6. ….. Examples of Property 1. Color 2. Size 3. Gender 4. Material 5. Season 6. ….. Examples of Property Car.Color – might be too broad Car.Interior.Color = Black Properties come after the object hierarchy An Analogy Property is what an object has 17 Methods in VBA 1. Start 2. Stop 3. Crash Examples of Method 1. PutOn 2. TakeOff Examples of Method How to start the car? Quickly or slowly? Car.Stop Quickly Car.Stop StopStyle:=Quickly Methods can have additional information An Analogy Methods can change properties The “Crash” method would change the size property of the car Method is what an object does 20 Methods Can Have Arguments Range(“A2”).Clear Sheet1.Copy No further arguments 1 optional argument (Shift) Examples Range("A2").Delete xlShiftToLeft Range("A3").Copy Range("B3") Range(“A2”).Delete 2 optional arguments (Before, After) which are optional but exclusive Range("C3").PasteSpecial xlPasteValues Sheet1.Copy After:=Sheet3 Sheet1.Copy , Sheet2 21 Find the Correct Property & Method F1 Press F1 when on an object, property or method to get help for the MSDN Microsoft Help Site It’s impossible to remember everything… Search the internet & online forums Use the Object Library (F2) Use the Macro Recorder to get object names, properties and methods Use IntelliSense – Let VBA suggest the right properties & methods. Check options from Tools / Options Use complete word to get suggestions (Ctrl + Space) Use the Immediate Window to query (e.g. color codes) or test code 22 Referencing Ranges, Worksheets & Workbooks Visual Basic Essential: How to work with cells, sheets & workbooks # Methods to Write to One or More Cells # Most Useful Range Properties & Methods # Finding the Last Row or the Used Region # Referencing Worksheets & Workbooks Correctly 25 Different Methods to Write to Cells Range("A2:C2").Value = "2nd" Range("A" & 6, "C" & 6) = "6th" Range("A1").Value = "1st" Range("A4,C4") = "4th" Range("A5", "C5") = "5th" Range("A3:C3,E3:F3").Value = "3rd" Using Rows, Columns & Range referencing Cells(1, 1).Value = "1st" Range(Cells(6, 1), Cells(6, 3)).Value = "6th" Range("A4:C7").Cells(4, 2).Value = "7th" Range("A1").Offset(7, 2).Value = "8th" Range("A1:B1").Offset(8, 1).Value = "9th" Range("LastOne").Value = "10th" Cells(1,1).Offset(7, 2).Value = "8th" Cell A10 is called “LastOne” in Name Manager ActiveCell.Value = "1st" 26 Referencing Entire Rows / Columns Range("16:16,18:18,20:20").RowHeight = 30 Cells.Columns.AutoFit Rows("12:14").RowHeight = 30 Range("H:H,J:J").ColumnWidth = 10 Range(Columns(1), Columns(3)).ColumnWidth = 5 Columns("E:F").ColumnWidth = 10 Using Range, Cells & Offset referencing Rows 12, 13 & 14 have a row height of 30 Rows 16, 18 & 20 are changed. 17 & 19 are not touched. Columns A, B & C have column width of 5 Columns E to F have a column width of 10 Columns H & J are changed. Column I is not touched. All columns are adjusted by autofit 27 4 Methods to Find the Last Row Range("K12").Value = Application.ActiveSheet.UsedRange.Rows.Count In these examples the results are written to a cell. Later we will store these in variables. 1 Use the End Property of the Range Object Range("K6").Value = Cells(Rows.Count, 1).End(xlUp).Row ‘or Range("K6").Value = Range(“A4”).End(xlDown).Row ‘Example for last column Range("K8").Value = Cells(4, Columns.Count).End(xlToLeft).Column 2 Use the CurrentRegion Property of the Range Object Range("K10").Value = Range("A4").CurrentRegion.Rows.Count 3 Use the SpecialCells Method of the Range Object 4 Use the UsedRange Property of the Worksheet Object Range("K11").Value = Cells.SpecialCells(xlCellTypeLastCell).Row 30 Copy & Resize Variably Sized Ranges Range("A4").CurrentRegion.Copy Range("J4") Range("A4").CurrentRegion.Copy Range("J20").PasteSpecial xlPasteValuesAndNumberFormats ‘to add more paste special options add a new line Range("J20").PasteSpecial xlPasteComments Range("A4").CurrentRegion.Offset(1, 0) _.Resize(Range("A4").CurrentRegion.Rows.Count - 1).Copy Range("A20") Range("A4:E10").Copy Range("J4") Or for a fixed range: Copy method for a variable sized range PasteSpecial method to use Excel’s Paste Special options Use the Resize property to return a changed range Copy & PasteSpecial Methods 31 How to Best Reference Worksheets Use the code name of the sheet (give your own code name in the property window) shDest.Range("A3").Value 2 advantages • User can be free to change the tab name • IntelliSense works perfectly with code names (doesn’t work with ActiveSheet because ActiveSheet could also be a chart sheet…) Worksheets("Destination").Range("A3").Value Worksheets(6).Range("A3").Value Use the Worksheets collection and refer to the tab by name Use the index number of the worksheet (can be risky) ActiveSheet .Range("A3").Value 32 2 1 3 Become familiar with Macros, VBE & the Object Model How to Reference Ranges, Worksheets & Workbooks Variables, Data Types Working with Collections & Making Decisions Useful VBA Functions Debugging code, Handling Errors & Procedure Scope Interacting with Other Applications (Word, PowerPoint, Outlook, PDF) Worksheet & Workbook Events ActiveX Form Controls & VBA UserForms Looping in VBA to Control the Flow of Code Working with Arrays Working with Files, Folders & Text Files Excel Tables, Formulas & Pivot Tables Function Procedures & Working with Charts More… 35 Understanding Variables & Data Types Visual Basic Essential: Working with Variables # What are Variables and Why Use Them # Data Types & Best Practice # Working with Object Variables # Re-using Variables in Other Procedures 36 Role of Assignment Equal sign does not mean equality here. It’s used to assign the result of the right-hand side to the left-hand side. Range("A1").Value = Date This is an assignment statement: This is also an assignment statement: Range("A1").Value = Range("A1").Value + 1 Variables help memorize the value on the right-hand side for you, and allow you to re-use them in your code. 37 Declaring Variables, Arrays & Constants …& the role of Option Explicit A group of similar variables that has one name can be declared as an Array Declaring variables by defining a suitable data type has the outcome: 1. Your code will run faster 2. Your code will be less prone to mistakes Dim LastRow As Long, FirstRow as Long Const myScenario As String = “Actual” Const ProfitCen As Long = 9999 Use Option Explicit (to minimize mistakes) In VBE, add a check-mark: Tools / Options / Require Variable Declaration 1 2 3 If you need to refer to a value that never changes, you can use Constants Dim myMonth(1 To 12) As String Dim myText As String 40 Using Object Variables Set Statement in VBA 1. Workbook Object 2. Worksheet Object 3. Range Object LastRow = Rows.Count– is the same as: Let LastRow = Rows.Count All “usual” VBA assignments are actually done with the LET statement. However this is an optional keyword. That’s why it’s usually not used. In case you come across it, you know what it does. Set NewBook = Workbooks.Add Dim NewBook As Workbook Dim NewSheet As Worksheet Dim NewRange As Range Variables can also hold objects. Common objects are:# To assign variables to objects, you need to use the SET statement# 41 Variable Scope Is the variable reusable in other procedures? Module: Variable exists for ALL Procedures within the Module. Dim is outside any procedure. Typically right below Option Explicit. Value is kept in memory after procedure completes. Procedure: Variables exists only when the procedure runs. Dim is inside the procedure. Memory is freed after procedure ends. Sub Defining_Variables () Dim LastRow As Long, FirstRow as Long ‘---code End Sub All Modules & Procedures: Variable exists for ALL Modules and ALL Procedures. Use the keyword “Public” to declare these variables. Can be declared in any Module before the first procedure. Value is kept in memory after procedure completes. Option Explicit Dim LastRow As Long, FirstRow as Long Sub Defining_Variables () ‘---code End Sub Option Explicit Public LastRow As Long, FirstRow as Long Sub Defining_Variables () ‘---code End Sub 42 Looping Through Collections & Making Decisions A VBA Essential for Efficient Macros # With…End With Constructs for Easier Coding # Looping Through Collections, i.e. sheets, cells in One Go # Making Decisions with IF & Select Case Constructs # Change the Program Flow with GoTo Statements 45 With…End With for Easier coding The less dots you have, the faster your code will run. The benefits of the With…End With construct are: Faster code writing Easier code maintenance Faster code execution Set myRange = Range("A10", "A" & Cells(Rows.Count, 1).End(xlUp).Row) With myRange.Font .Name = “Arial” .Size = 12 .Bold = True End With After Set myRange = Range("A10", "A" & Cells(Rows.Count, 1).End(xlUp).Row) myRange.Font .Name = “Arial” myRange.Font.Size = 12 myRange.Font.Bold = True Before 46 For…Each to Loop Through Collections Looping through Worksheets, Ranges etc. in One Go VBA provides an easy method to loop through a collection of similar objects. For example: Execute code for all Worksheets in a Workbook Follow instructions for all Cells inside a Range Run code for each Comment inside the Worksheet Comments collection Dim Sh As Worksheet For Each Sh In ThisWorkbook.Worksheets Sh.Protect "test“ Next Sh Dim cell As Range For Each cell In ActiveSheet.UsedRange ‘[Instructions – you can use IF here to check whether a condition ‘ is met – also to exclude parts of the collection] Next cell 47 Goto Statement to Change Program Flow Use this mainly for Error Handling. Otherwise your code can become too confusing to understand. You can skip code lines with the Goto Statement. Why would you want this? Mainly for error handling Execute a different part of code depending on a condition Sub Simple_GoTo () Range("D3").Value = "" If IsError(Range("B3")) Then GoTo GetOut Range("C3").Value = Range("B3").Value Exit Sub GetOut: Range("D3").Value = "You have an error in the cell“ End Sub To use GoTo Type the name of the label with colon (or number without colon) You might need to Exit sub before the label if you have a message box or another VBA statement 50 Key Takeaways: Collections & Decisions 1 Use With … End With Construct To optimize code writing and execution 2 Use For…Each Loop To loop through members of a collection. 4 Use GoTo Statement to Jump to Different Sections of Code Use labels to put “bookmarks” in part of code, such as “Leave:” then use GoTo Leave to jump to that section of code. 3 Use Select Case Statement once IF gets too Complex Put the most likely cases on top 51 2 1 3 Become familiar with Macros, VBE & the Object Model How to Reference Ranges, Worksheets & Workbooks Variables, Data Types Working with Collections & Making Decisions Useful VBA Functions Debugging code, Handling Errors & Procedure Scope Interacting with Other Applications (Word, PowerPoint, Outlook, PDF) Worksheet & Workbook Events ActiveX Form Controls & VBA UserForms Looping in VBA to Control the Flow of Code Working with Arrays Working with Files, Folders & Text Files Excel Tables, Formulas & Pivot Tables Function Procedures & Working with Charts More… 52 Text handling Description InStr Returns the position of one string within another LCase Converts string to lowercase Left Returns the left hand side of a string based on specified number of characters Len Returns the number of characters in a string Mid Returns a specified number of characters from a string Replace Returns a string and replaces a subset of the string with another string Right Returns the right hand side of a string based on specified number of characters Space Returns a string the contains spaces StrConv returns a string converted to uppercase, lowercase, proper case or Unicode. Trim Returns a string without leading or trailing spaces UCase Converts string to uppercase Constants Description VbNewLine Creates a new line in message box VbNullString Returns a zero length string VbTab Adds a tab space in message box Useful VBA Functions Text Handling (String manipulation) 55 Date handling Description Date Returns the current date Day Returns the day of the month of a date Hour Returns the hour of a time value IsDate Returns true if variable is a Date type Minute Returns the minute of a time value Month Returns the month of a date MonthName Returns a string for the month of a date (abbreviation possible) Now Returns the current date and time Second Returns the seconds portion of time Time Returns the current system time Timer Returns the number of seconds since midnight (good for adding a timer in code) Weekday Returns a number for the day of the week WeekdayName Returns a string for the day of the week Year Returns the year of a date Useful VBA Functions Date Handling 56 Formatting & Converting Description CDate Converts expression to Date data type CInt Converts expression to Integer data type CLng Converts expression to Long data type CStr Converts expression to String data type Format Shows an expression in the desired format Str Returns a string from a number Val Returns a number (double) from a string Number handling Description Abs Absolute value of a number FormatNumber Formats an expression as number FormatPercent Formats an expression as percentage IsNumeric Returns True if a cell or value is numeric Round Rounds a number Useful VBA Functions Formatting, Converting & Number Handling 57 VBA’s InputBox Function Dim myInp As String myInp = VBA.InputBox("Please input subtitle", "Subtitle please...") If myInp = "" Then Exit Sub Range("A2").Value = Excel.WorksheetFunction.Proper(myInp) If you’d like to use the answer in your code, and the answer should be a number, you need to convert it to a number with a conversion function. For example the Val function. To validate if the input is a number, you can use IsNumeric function Input Box always returns a string. If you write the result of the input box to a cell, Excel will automatically convert the answer to the correct type. For example if you input a number, it will be recognized as a number in the cell. Easily create input dialog boxes to capture user input. 60 Excel’s InputBox Method 3 benefits of using the Input Box Method 1 You can specify the data type for input (not restricted to string) Code Description 0 Formula 1 Number 2 String 4 Boolean 8 Range 16 Error value 64 Array of values 1+2 = 3 Number + String Excel performs a validation automatically2 You can select ranges3 CName = VBA.InputBox _ ("Please input new Customer Name", "Customer Master") Set cRange = Application.InputBox _ ("Count the number of cells with values", "Cells with values", , , , , , 8) 61 Key Takeaways: Built-in Functions 1 Review the List for Useful VBA Functions Take some time and review the list and test some of the functions you think could come in handy for your projects. 2 MsgBox Function Use the message box to inform the user or get a “Yes”, “No” answer from them. You can also use the message box for debugging your code. 4 Excel’s InputBox Method Excel offers its own version of the input box. It has the additional feature of allowing the user to select a range or do basic validation checks. 3 VBA InputBox Function VBA’s Input box function is a quick way to allow the user to interact with your code. If you’d like to add some basic validation checks – i.e. for numbers or text, use Excel’s InputBox method instead. 62 Methods to Debug Your Code F8 or select Debug from the menu and then Step Into.1 To help you Pinpoint Errors… in addition to the MsgBox 2 Add breakpoints with F9 or Debug menu / Toggle Breakpoint. You can play the code until a certain place and then you can step in with F8. Add as many as you need. 3 Hover your mouse on a variable to see its result. 4 Use the Immediate Window. Type “Debug.Print” followed by your variable name in the code. This writes the result to the immediate window. You can also type in code directly in this window. Use the “?” to get the answer. Activate it from the View tab. 5 Use the Locals Window to see the value and characteristics associated with each variable. Activate it from the View tab. 6 Use the Watch window to keep your eye on certain variables. Right-mouse click on a variable or statement and “add to watch”. Alternatively drag to the window. You can change the variable property directly by typing it in. Activate watch from the View tab. 65 Error Handling: Different Methods Common error handling examples Sub Jump_to_End() On Error GoTo Leave [code instructions] Leave: End Sub 1 Sub Resume_Then_Normal() On Error Resume Next [code instructions] On Error GoTo 0 [code instructions] End Sub 2 Sub Handle_Based_on_Error_Type() On Error GoTo ErrorHandle [code instructions] Exit Sub ErrorHandle: Select Case Err.Number Case 424 Exit Sub Case Else MsgBox "An error has occurred." End Select End Sub 3 Uses a label to jump to the end of the code the moment an error occurs. Suppresses certain errors and then resumes normal error handling for the remaining instructions. More detailed error handing by type. Add “Exit sub” to leave the macro if error-free. 66 Faster & Efficient VBA Code Suppressing Pop-ups & flickering screen With Application .StatusBar = "Wait“ .ScreenUpdating = False .Calculation = xlCalculationManual .DisplayAlerts = False End With Suppress With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .DisplayAlerts = True .CutCopyMode = False .StatusBar = "" End With Restore • Use the Status bar to let the user know the macro is running • Screen flickering • Formula calculations • Excel alerts – for example when deleting worksheets or closing a workbook. Use this block of code when writing to cells, working with different worksheets or workbooks & other longer tasks.(In case you used PasteSpecial) 67 2 1 3 Become familiar with Macros, VBE & the Object Model How to Reference Ranges, Worksheets & Workbooks Variables, Data Types Working with Collections & Making Decisions Useful VBA Functions Debugging code, Handling Errors & Procedure Scope Interacting with Other Applications (Word, PowerPoint, Outlook, PDF) Worksheet & Workbook Events ActiveX Form Controls & VBA UserForms Looping in VBA to Control the Flow of Code Working with Arrays Working with Files, Folders & Text Files Excel Tables, Formulas & Pivot Tables Function Procedures & Working with Charts More… 70 You’ve completed the first major milestone of the course! CONGRATULATIONS I hope you’ve learnt new techniques which you can apply to your projects and I hope you’re enjoying the process as much as I am! 71 Looping in VBA Controlling the Flow of Code # For…Next Counter Loops # Do Until / Do While Loop # Find Method for Quicker Results # Time your Code (to pick the faster version) 72 Find Method for Quicker Results FIND method provides a quick way to find the answer without Looping Sub Many_Finds() Dim CompID As Range, FirstMatch As Variant, i As Long Dim Range("D3:D6").ClearContents i = 3 Set CompID = Range("A:A").Find(What:=Range("B3").Value, _ LookIn:=xlValues, LookAt:=xlWhole) If Not CompID Is Nothing Then Range("D" & i).Value = CompID.Offset(, 4).Value FirstMatch = CompID.Address Do Set CompID = Range("A:A").FindNext(After:=CompID) If CompID.Address = FirstMatch Then Exit Do i = i + 1 Range("D" & i).Value = CompID.Offset(, 4).Value Loop End If End Sub FIND with DO LOOP for Many Matches From MSDN.Microsoft.com 75 Add a Timer to Test & Speech to Inform Adding a timer (VBA function) helps you test different versions of code for the same task [code instructions] Application.Speech.Speak "Job Done!" End Sub 1. Write your code 2. Add a Timer 3. Comment out the current code 4. Write new code and test 5. Use the code version that runs fastest 6. Remove the Timer or comment out Allowing your computer to “speak” is a good way of informing the user that a long procedure is now completed. Speakers should be turned on. Dim Start Start = Timer 'seconds since midnight [code instructions] Debug.Print Timer - Start 76 General Description Const Declare a constant Dim Declares variables End Exits program – also ends procedures, with statements, etc. Function Declare a function procedure Kill Deletes a file Let Assigns a variable to an expression (is optional - can be omitted) Like Returns True if one string can be matched with another Load Loads an object (like a userform) but doesn’t show it Mid Replaces characters in a string with other characters Option Explicit Forces variable declaration Public Declares a variable to be used in all procedures ReDim Change the dimension of the array Set Assign an object to a variable Sub Declares the name of the sub procedure Useful VBA Statements Common Statements 77 Key Takeaways: LOOPING IN VBA 1 For … Next Counter Loop Very powerful and flexible way of looping through cells. It’s safer to use than the Do loop: The loop only runs for a specific number of times depending on the lower and upper values of the control variable. 2 Do Loop Variations include Do…Until, Do…While and just Do (with a check for a condition when the loop can be exited). These come in handy when you don’t know the number of times the loop should run. Tip → Use F8 first before running the code to make sure it works properly. 4 Use a Timer & Speech to Inform Use VBA’s Timer function to test different variations of code, in case you aren’t sure which one is more efficient. Speech.Speak method is a good way of vocally informing the user the macro has finished running. 3 Find Method Find method can be faster than the For…Next and Do Loop methods when looking for one or many matches. 80 2 1 3 Become familiar with Macros, VBE & the Object Model How to Reference Ranges, Worksheets & Workbooks Variables, Data Types Working with Collections & Making Decisions Useful VBA Functions Debugging code, Handling Errors & Procedure Scope Interacting with Other Applications (Word, PowerPoint, Outlook, PDF) Worksheet & Workbook Events ActiveX Form Controls & VBA UserForms Looping in VBA to Control the Flow of Code Working with Arrays Working with Files, Folders & Text Files Excel Tables, Formulas & Pivot Tables Function Procedures & Working with Charts More… 81 Working with Arrays For Faster Procedures # One Dimensional Fixed Arrays # Working with Dynamic Arrays (& preserving values) # Two Dimensional Arrays # Variant Arrays 82 Key Takeaways: Arrays 1 One Dimensional Array This is similar to highlighting one row or one column in Excel. Fixed one-dimensional array is defined during the DIM statement: Dim MonthArray(1 To 12) As String. 2 Two Dimensional Array This is similar to highlighting a matrix which includes a few columns and rows in Excel. Think of the rows as the 1st dimension in an array and the columns as the 2nd dimension. 4 Variant Array This is similar to Excel arrays. A range of cells is defined as a variant. It can be directly manipulated with a loop and values written back to cells in one go. This is a faster method than using a For…Next loop. 3 Dynamic Array The exact size of the array is defined during code execution. Use the REDIM statement to define the size and then fill the array. 85 2 1 3 Become familiar with Macros, VBE & the Object Model How to Reference Ranges, Worksheets & Workbooks Variables, Data Types Working with Collections & Making Decisions Useful VBA Functions Debugging code, Handling Errors & Procedure Scope Interacting with Other Applications (Word, PowerPoint, Outlook, PDF) Worksheet & Workbook Events ActiveX Form Controls & VBA UserForms Looping in VBA to Control the Flow of Code Working with Arrays Working with Files, Folders & Text Files Excel Tables, Formulas & Pivot Tables Function Procedures & Working with Charts More… 86 Working with Files, Folders & Text Files Provide Flexibility & Create Data Exports # Check if a File / Folder Exists (DIR) # Allow the User to Select a File or Folder # Create CSV Files # Work with Text Files (for more control) 87 User Selects a Folder (to Loop Through) FileDialog Property provides an easy way to allow the user to select a folder. You can write a loop to go through each file inside the folder. Sub Loop_Inside_Folder() Dim FileDir As String Dim FiletoList As String With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Please select a folder" .ButtonName = "Pick Folder" 'Cancel show value = 0, -1 there was a selection If .Show = 0 Then Exit Sub Else FileDir = .SelectedItems(1) & "\" End If End With FiletoList = Dir(FileDir & "*xls*") Do Until FiletoList = "" FiletoList = Dir Loop End Sub Get Folder Name Calling the Dir function again inside the loop without any arguments moves on to the next file in the folder. 90 Export Sheets as CSV Sub Save_as_CSV() Dim NewBook As Workbook Dim FileName As String Application.ScreenUpdating = False Application.DisplayAlerts = False FileName = Application.ThisWorkbook.Path & "\TestTextCSV.csv" Set NewBook = Workbooks.Add ShCSV.Copy before:=NewBook.Sheets(1) With NewBook .SaveAs FileName:=FileName, FileFormat:=Excel.xlCSV .Close End With Application.ScreenUpdating = True Application.DisplayAlerts = True MsgBox "Your CSV file was exported.", vbInformation End Sub Export as CSV To export a sheet as CSV, first copy the sheet to a new Workbook (make any adjustments necessary) and the save this workbook as a csv file. Make sure to turn off display alerts. 91 Writing and Reading a Text File VBA Open Statement (not the Open Method of Workbook) opens a file for reading or writing. This gives your more control over the layout (for example the delimiter) Required statements are: • Pathname – path and name of file to be opened • Mode – Input (read the file only), Output (write or read the file), Append (add to the bottom of file) • Filenumber – the next available file number. Use #1 if this is the first file opened or use the FreeFile Function to get the next available file number Open pathname For mode [ Access access ] [ lock ] As [ # ] filenumber [ Len = reclength ] Write - This writes a line of text to the file surrounding it with quotations Print - This writes a line of text to the file without quotations FileName = Application.ThisWorkbook.Path & "\ProjectActivity.csv" Open FileName For Output As #1 ' [Instructions to loop through range] Print #1, myValue Close #1 92 Excel Tables, Formulas & Pivot Tables A VBA Essential for Efficient Macros # Using Excel Formulas in VBA # Excel Tables - Use the Right Syntax # Pivot Tables - What you Need to Know 95 Using Excel Formulas In VBA Dim LastRow As Long LastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row Range("F9").FormulaR1C1 = "=IF(VALUE(LEFT(RC[-4],1))=8,RC[-1]-50,"""")" Range("F9").AutoFill Destination:=Range("F9:F" & LastRow), Type:=xlFillDefault Application.Calculate To ensure compatibility with different Excel languages, use the macro recorder to record formulas. The formulas are always recorded with the default English reference library. These work on all the different languages of Excel. FormulaR1C1 property uses the row and column numbering to refer to cells. Formula property uses the A1 Type of referencing (although it can interpret R1C1 referencing as well). To display formulas in local language, use the FormulaLocal property. E.g: Range(“F20”).FormulaLocal instead of Range(“F20”).Formula 96 Table Task VBA Code Declaring Tables Dim myTable As ListObject Set myTable =Activesheet.ListObjects(“Table1") Whole Table myTable.Range.Select All Data – No Headers myTable.DataBodyRange.Select Count Rows myTable.Range.Rows.Count Specific Row myTable.ListRows(5).Range.Select Specific Column exclude Headers myTable.ListColumns(“Quantity”).DataBodyRange.Select Header Only myTable.HeaderRowRange.Select Add a Row to the bottom myTable.ListRows.Add , False Add a 2nd Row myTable.ListRows.Add 2 Add a Column to the end myTable.ListColumns.Add Add a 2nd Column myTable.ListColumns.Add 2 Add a Column to the end myTable.ListColumns.Add Rename the last Column myTable.ListColumns(myTable.ListColumns.Count).Name = “NEW" Add Formula to Row 1 Column 6 myTable.DataBodyRange(1, 6).FormulaR1C1 = "=(“put formula”)" Working with Excel Tables 97