VBA & macros
Automate repeat work with macros and VBA code.
- Excel VBA: Clear the Clipboard After Copy and PasteTo clear Excel’s copy mode after a VBA copy-and-paste operation, run Application.CutCopyMode = False after the paste. This removes Excel’s moving dashed…
- Excel VBA: How to Check Whether a Workbook Is OpenTo check whether a workbook is open in Excel VBA, loop through Application.Workbooks and compare each open workbook’s name or full path…
- Excel VBA: Check Whether a Worksheet ExistsTo check whether a worksheet exists in Excel VBA, loop through the target workbook’s Worksheets collection and compare each sheet name. This…
- Excel VBA: Check Whether a Worksheet Name Exists SafelyTo check whether a worksheet exists in Excel VBA, try to assign the named item from the target workbook’s Worksheets collection, then…
- Excel VBA: Check Whether a Dictionary Key ExistsUse a VBA Dictionary’s Exists method to check for a key: If dict.Exists(keyToFind) Then. It returns True when the key is present…
- Excel VBA: Check If a String Contains a Substring in a CellIn Excel VBA, use InStr to check whether one string contains another: it returns a position greater than zero when it finds…
- Excel VBA: Check Whether a String Is Empty or BlankIn Excel VBA, use Len(text) = 0 to check whether a string has no characters. If you also want to treat spaces…
- Excel VBA: Check Whether a Dynamic Array Is AllocatedNo—VBA arrays are not objects, so If myArray Is Nothing is not a valid way to test one. To check a dynamic…
- Excel VBA: Check If AutoFilter Is Active on a SheetIn Excel VBA, check Worksheet.AutoFilterMode to find out whether AutoFilter arrows are displayed, and check Worksheet.FilterMode to find out whether the worksheet…
- Excel VBA: Center a UserForm on Screen AutomaticallyTo center an Excel VBA UserForm on the screen, set its StartUpPosition property to 2 - CenterScreen before displaying it. You can…
- Excel VBA MsgBox Font Size: Change It or Use a FormYou cannot set the font size of Excel’s built-in VBA MsgBox directly: its text uses the standard Windows dialog appearance, and VBA…
- Excel VBA Build Array: Methods, Examples, and LimitsTo build an array in Excel VBA, assign the Array function to a Variant, or declare a typed array and fill its…
- Excel VBA Can’t Find Project or Library? Fix the ErrorIf Excel VBA says “Can’t find project or library,” the VBA project usually has a reference to a library that is missing,…
- Excel VBA Break Loop: Keyboard Shortcuts and FixesTo interrupt a running Excel VBA loop on Windows, press Ctrl+Break (sometimes labeled Pause/Break); on a Mac, press Command+Period (⌘.). Excel usually…
- Excel VBA UserForm: Bring It to the FrontTo bring a modeless Excel VBA UserForm forward on Windows, show it with vbModeless, then use the Windows API SetWindowPos to move…
- Excel VBA: Bring a Workbook Window to the Front ReliablyTo bring an open workbook to the front with Excel VBA, activate Excel and then activate the workbook: call Application.Activate followed by…
- Excel VBA Arrays vs Collections vs DictionariesIn Excel VBA, use an array for a compact, indexed set of values, a Collection for an ordered group of items you…
- Excel VBA AutoFilter: Copy Visible Results to Another SheetYes. In Excel for Microsoft 365 on Windows, apply AutoFilter to the source range, then copy its visible cells to a destination;…
- Excel VBA AutoFilter Method Failed? Causes and FixesYes—“AutoFilter method of Range failed” is usually a VBA runtime error caused by an invalid or unsuitable range, an incorrect field number,…
- Excel VBA Array Maximum Size: Limits and Practical TipsThere is no single, guaranteed maximum number of elements for an Excel VBA array: its usable size depends mainly on available memory,…
- Excel VBA Array Methods: Create, Resize, and SearchExcel VBA does not provide a single built-in “array methods” object: instead, it offers array functions and statements such as Array, Split,…
- Excel VBA: Highlight the Active CellTo highlight the active cell in Excel for Microsoft 365 on Windows, put a Worksheet_SelectionChange event in that worksheet’s code module and…
- Excel VBA: Add a New Worksheet With a Specific NameIn Excel VBA, add a worksheet with Worksheets.Add, then set its Name property. For example, the key steps are Set ws =…
- Excel VBA Array Delete: How to Remove Items SafelyIn Excel VBA, use Erase to clear an entire array; it does not remove just one element. To delete one item, shift…
- Excel VBA Array Filter: Methods, Code, and ExamplesTo filter a one-dimensional array of strings in Excel VBA, use the built-in Filter function, for example Filter(names, "Ava", True, vbTextCompare). It…
- Excel VBA Active Workbook: Meaning and ExamplesIn Excel VBA, ActiveWorkbook refers to the workbook currently active in Excel’s window. Use it when a macro should act on whichever…
- Excel VBA Active Worksheet: How It Works and When to Use ItIn Excel VBA, ActiveSheet refers to the sheet currently selected in the active workbook. It is not a special “active worksheet” command:…
- Excel VBA Active Worksheet Reference ExplainedIn Excel VBA, use ActiveSheet to refer to the sheet currently active in the active workbook. For a dependable macro, assign that…
- Excel VBA Active Range: What Selection Really MeansExcel VBA does not have a built-in ActiveRange property. To refer to the range currently selected in Excel, use Selection; to refer…
- Excel VBA Active Pivot Table: How to Find and ControlIn Excel VBA, get the PivotTable associated with the active cell by using ActiveCell.PivotTable; Excel does not provide a single application-wide ActivePivotTable…