VBA & macros
Automate repeat work with macros and VBA code.
- Excel VBA Function Multiple Outputs: Practical MethodsYes. An Excel VBA function can provide multiple outputs by returning an array, or VBA code can use ByRef arguments or a…
- Excel VBA Expected Expression Error: Causes and FixesIn Excel VBA, Expected: expression is a compile error: VBA reached a place where it needs a value or calculation, but found…
- Excel VBA Function Key: Run Macros and Assign ShortcutsIn Excel VBA, press F5 in the Visual Basic Editor to run the current procedure; press F8 to step through it one…
- Excel VBA Cross Product: Create Cartesian PairsExcel has no built-in worksheet function named CROSSPRODUCT for pairing two lists, but you can create a VBA user-defined function that returns…
- Excel VBA Default Parameter Values and Optional ArgumentsIn Excel VBA, give a parameter a default by declaring it Optional and assigning a value in the procedure signature, such as…
- Excel VBA Exit Function: When and How to Use It SafelyIn Excel VBA, Exit Function stops the current function immediately and returns control to the statement that called it. Before using it,…
- Excel VBA Function Arguments: Syntax and ExamplesIn Excel VBA, function arguments are the values or variables you pass to a function so it can do its work; the…
- Excel VBA Format Date Values: Examples and FixesTo format a date in an Excel cell with VBA, set the cell’s NumberFormat property, for example: Range("A1").NumberFormat = "yyyy-mm-dd". This changes…
- Excel VBA: Format a Range as a DateTo format a range as dates with Excel VBA, assign a date format to its NumberFormat property, for example Worksheets("Data").Range("B2:B100").NumberFormat = "dd-mmm-yyyy".…
- Excel VBA Date and Time Formatting: Practical ExamplesTo format a date and time in an Excel cell with VBA, set the cell or range’s NumberFormat property, for example: Range("A2:A20").NumberFormat…
- Excel VBA Date Formatting: Examples and FixesTo format a date in Excel VBA, use Format$ when you need formatted text, or set a cell’s NumberFormat when you want…
- Excel VBA Date String Formatting ExplainedIn Excel VBA, use Format$ to turn a date value into a formatted text string, such as Format$(Date, "yyyy-mm-dd"). To change how…
- Excel VBA For Loop: Skip to the Next IterationExcel VBA does not have a built-in Continue For statement. To skip the rest of the current iteration and let the loop…
- Excel VBA Format Date as dd mm yyyy hh mm ssTo keep a real Excel date/time value, set a cell’s number format with VBA, for example Range("A1").NumberFormat = "dd mm yyyy hh:mm:ss".…
- Excel VBA Format Date as DD/MM/YYYY in a WorksheetTo display Excel dates as dd/mm/yyyy with VBA, set the range’s NumberFormat property, for example: Range("B2:B100").NumberFormat = "dd/mm/yyyy". This changes how valid…
- Excel VBA For Loop Backwards: Syntax and ExamplesYes. To run an Excel VBA For loop backwards, use For counter = start To finish Step -1; for example, For i…
- Excel VBA: Loop Through Every Sheet in a Workbook SafelyTo run VBA on every worksheet in a workbook, use a For Each loop with the workbook’s Worksheets collection. In a macro…
- Excel VBA For Each Array: How to Loop Through ValuesYes—Excel VBA can use For Each to visit every element in an array. Declare the loop variable as Variant, then write For…
- Excel VBA Fast Loop: Speed Up Repetitive Macro CodeTo make an Excel VBA loop fast, avoid reading or writing worksheet cells one at a time. Read the needed range into…
- Excel VBA File Last Modified Date: Get It ReliablyIn Excel VBA, use the built-in FileDateTime function to read a file’s last-modified date and time. For the workbook containing the macro,…
- Excel VBA FLOOR Function: Round Down to a MultipleExcel VBA does not have a built-in function named FLOOR that you can call directly, but you can use Excel’s worksheet function…
- Excel VBA: Extract the Year from a Date in a WorksheetTo extract a year from a date in Excel VBA, use the built-in Year function: pass it a valid date value, and…
- Excel VBA Factorial Function: Code, Limits, and UseExcel already calculates factorials with the FACT worksheet function, but you can also create a VBA function for a reusable custom calculation.…
- Excel VBA: Extract Text From a StringTo extract text from a string in Excel VBA, locate the relevant characters with InStr and return them with Mid$. For example,…
- Excel VBA: Export a Workbook to a New File FormatTo export an Excel workbook with VBA, use Workbook.SaveAs to save the open workbook in a different file format, or Workbook.SaveCopyAs to…
- Excel VBA: Export a Worksheet to a New FileIn Excel for Microsoft 365 on Windows, VBA can export a worksheet by copying it into a new workbook and saving that…
- Excel VBA Export Range to CSV, Text, or Another SheetTo export an Excel range with VBA, copy it to a temporary workbook and save that workbook as a CSV file; for…
- Excel VBA Erase Array: What It Does and When to Use ItIn Excel VBA, use Erase arrayName to reset an array: it releases the allocated storage of a dynamic array, but resets the…
- Excel VBA: How to End a LoopTo end a VBA loop early, use Exit For inside a For loop or Exit Do inside a Do loop. Use Next…
- Excel VBA: How to Exit a For Next Loop Early in Your MacroYes. In Excel VBA, put Exit For inside a For...Next loop to leave it as soon as a condition is met; execution…