VBA & macros
Automate repeat work with macros and VBA code.
- Excel VBA: Open a CSV File and Control the Import SafelyTo open a CSV file with Excel VBA and control how its columns are parsed, use Workbooks.OpenText. Set the file path, encoding,…
- Excel VBA: Open a CSV and Copy Its Data to a SheetTo open a CSV file with Excel VBA and copy its data into a worksheet, use Workbooks.OpenText to import the comma-delimited file,…
- Excel VBA: How to Open an Excel File as Read-OnlyTo open an Excel workbook as read-only with VBA, pass ReadOnly:=True to Workbooks.Open. For example, Set sourceBook = Workbooks.Open(Filename:=filePath, ReadOnly:=True) opens the…
- Excel VBA On Error GoTo Next: What It Really DoesExcel VBA does not have an On Error GoTo Next statement. The syntax people usually mean is On Error Resume Next: it…
- Excel VBA On Error Resume Next: Safe Use and ExamplesIn Excel VBA, On Error Resume Next tells a procedure to continue with the statement immediately after the one that caused a…
- Excel VBA: How to Open a Binary FileYes. In Excel VBA on Windows, open a file with Open ... For Binary Access Read and use Get to read its…
- Excel VBA On Error Exit Sub: How Error Handling WorksIn Excel VBA, On Error Exit Sub is not a valid error-handling statement. To stop a procedure after an error, route execution…
- Excel VBA On Error GoTo: How Error Handling WorksIn Excel VBA, On Error GoTo LabelName sends execution to a named error-handling section when a run-time error occurs in that procedure;…
- Excel VBA On Error GoTo: Handle Macro Errors the Right WayIn Excel VBA, On Error GoTo label tells a procedure to jump to a named error-handling section when a run-time error occurs.…
- Excel VBA Macro to Copy Data Between SheetsTo copy data between worksheets with an Excel VBA macro, assign the source range’s values to a destination range of the same…
- Excel VBA Next Without For Error: Causes and FixesThe Excel VBA compile error “Next without For” means VBA has reached a Next statement without finding a matching, currently open For…
- Excel VBA Macro to Filter Data: Code and ExamplesTo filter a worksheet with an Excel VBA macro, define the full range including its header row, then call its AutoFilter method…
- Excel VBA on Mac: How to Open Files with a MacroYes. Excel for Mac can open a workbook from VBA with Workbooks.Open; give it the file’s full Mac path, such as /Users/yourname/Documents/Report.xlsx.…
- Excel VBA Macro Best Practices for Reliable WorkbooksFor reliable Excel VBA macros, make each procedure do one clear job, declare variables with Option Explicit, qualify workbook and worksheet references,…
- Excel VBA Out of Memory Error: Causes and FixesAn Excel VBA “Out of memory” error usually means Excel cannot allocate enough usable memory for the macro’s next operation—not necessarily that…
- Excel VBA: Loop Through All Files in a FolderTo loop through files in a folder with Excel VBA, use the Dir function to get the first matching file, then call…
- Excel VBA: Loop Through Files in a Folder with DirIn Excel VBA, use the Dir function to find files that match a folder path and filename pattern, then repeat with Dir$()…
- Excel VBA Insert Image: Code and OptionsTo insert an image with Excel VBA, call the worksheet’s Shapes.AddPicture method, supplying the image file path and its position. Use LinkToFile:=msoFalse…
- Excel VBA: How to Stop a Running MacroTo stop a running Excel VBA macro on Windows, press Ctrl+Break; on a Mac, press Command+period (.). If Excel does not respond…
- Excel VBA: How to Check Whether Cell Colors Match ExactlyYes. In Excel VBA, compare the cells’ Interior.Color values; they are numeric color values, so an equality test tells you whether their…
- Excel VBA If Statement Without Then: What Works?No: an Excel VBA If statement must include the keyword Then. Write If condition Then before the action or block of code;…
- Excel VBA Format Number to 2 Decimal PlacesTo make Excel display numbers with exactly two decimal places, set a cell or range’s VBA NumberFormat property to "0.00". For example,…
- Excel VBA: Get the Last Row Number ReliablyTo get the last nonempty row in a particular column, start at the bottom of that worksheet column and move upward to…
- Excel VBA Format Cell Color: Fill, Font, and RulesTo format a cell’s fill color with Excel VBA, set its Interior.Color property; for example, Range("A1").Interior.Color = RGB(255, 0, 0) makes A1’s…
- Excel VBA: Find the Last Row With a Specific ValueTo find the last row containing a particular value, search the target column with VBA’s Range.Find method, set SearchDirection:=xlPrevious, and check whether…
- Excel VBA: Find the Last Used Row in Any WorksheetIn Excel VBA, use the worksheet’s Find method to locate the last cell containing content anywhere on the sheet, then read its…
- Excel VBA Format Cell as Number: Examples and TipsTo format cells as numbers with Excel VBA, assign a number-format code to the range’s NumberFormat property. For example, Range("B2:B100").NumberFormat = "#,##0.00"…
- Excel VBA: Find the Last Row with Data in a RangeTo find the last row containing data within a particular Excel range, use the range’s Find method with a bottom-to-top, row-wise search.…
- Excel VBA: Find the Last Row in a RangeTo find the last populated row in a specified Excel VBA range, use the range’s Find method with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. For…
- Excel VBA: Find the Last Row of a TableTo get the last data row of an Excel table (a VBA ListObject), use its DataBodyRange: the last worksheet row is tbl.DataBodyRange.Row…