List All Vba Environment Variables
This VBA Environment function grabs information about your operating system and returns the information as a string. The Environ function is useful for custo...
This VBA Environment function grabs information about your operating system and returns the information as a string. The Environ function is useful for custo...
Function to test if the workbook is protected.
Function to tests if the active sheet is protected.
Tests if any sheets in the active workbook are protected with or without a password.
Function to test an entire column or row has been selected.
Function to get the selected sheet names with examples.
Function to check if multiple named ranges exist
Some examples of VBA constants, useful when developing programs / add-ins.
Good if you are running a macro that will affect cell values
A whole heap of declarations for 32 and 64 bit operating systems.
Sub ReadOnlyCheck() Dim Answer As Long 'Check to see if file is Read-Only If ThisWorkbook.ReadOnly = TRUE Then Answer = MsgBox("Someone Is curren...
This code snippet is useful to check if the selection is a range of cells.
Easily add a custom right click menu for both normal ranges and in tables! First we need to put two codes into the ThisWorkbook Module, these 2 codes call ma...
An easy user defined function (UDF) to get the nth word from cell. I often use this to get first and last names when the full name is in a single cell.
A GUID (or UUID) is an acronym for ‘Globally Unique Identifier’ (or ‘Universally Unique Identifier’). It is a 128-bit integer number used to identify resourc...
User Defined Function to get the unique count in a range.
There are a few ways to convert Numbers stored as Text using Formula. You can use: =VALUE() or =NUMBERVALUE() for floating point Numbers. =INT() if you ...
Let’s image that the following path is in A1: C:\Users\foo\Documents\parent.folder\foo.bar.txt
Convert text to lower case Example: =LOWER("hello") Result: hello
You can always use the Insert –> Symbols method to enter characters onto your spreadsheet but you can also add them via a formula.
These formulas require that the workbook is saved
Combine these 3 functions to completely clean your data: =TRIM(text) =CLEAN(text) =SUBSTITUTE(text,” “,””)
What this code allows you to do is circumvent testing (ie looping) each individual cell and handling trimming (removing leading and ending spaces) and cleani...
You will be prompted with an input-box for the range of cells and for the leading text.
Join multiple string cells using one of 2 options, the code below shows how to choose the two, it boils down to what the user finds easier to read.
Here is a method to repeat the values in Column ‘A’, (n) number of times required in Column ‘B’ outputting the results in Column ‘C’. The Formula is an Array...
This will show how to use VBA to paste values in Excel instead of formulas. You can bind this VBA macro to the Ctrl+Shift+V keyboard shortcut to make pasting...
Manually typing each option for a drop-down list in word (drop-down list content control) is time consuming, but there is no native way to import a list you’...
When you want to just show numbers as 9.1 M or 830 K
This is one of my favourites and I use it all the time. Type a bunch of sheet names in a list of cells, select those cells then run this macro.
The below 2 examples create a named range for either a workbook scope or worksheet scope. Below the subroutines is more information on the differences and ad...
Obtaining Row information is easy since Rows are always Numbers. Column Letters that can be used in a Range are a little more tricky. I have many methods to ...
What this code allows you to do is circumvent testing (ie looping) each individual cell and handling trimming (removing leading and ending spaces) and cleani...
This one is pretty sweet. It creates a nicely formatted TOC sheet and a back button on each sheet that links to the TOC. If you happen to add a sheet, simply...
Loop through each sheet in all open workbooks and remove the page break lines
These formulas are great for when you have to find the week ending date or even the fortnight ending date for a specific item. Week ending dates don’t have a...
The INDIRECT function returns a reference to a range. You can use this function to create a reference that won’t change if row or columns are inserted in the...
From a list of 30 people with an assigned value, generate a smaller list that are the top n values in that list.
Learn how to calculate commissions for a basic tiered plan and rate table using the VLOOKUP function.
Let’s image that the following path is in A1: C:\Users\foo\Documents\parent.folder\foo.bar.txt
We can expand on the Index and Match formula and use an array formula to look up a value based on multiple criteria. This is an array formula so need to pre...
These formulas require that the workbook is saved
Generate a list of items based on a criteria that doesn’t show any blanks or spaces. 20 Fruits and Vegetables listed below, choose either a fruit or vegetabl...
This tutorial demonstrates the key strengths of Excel’s INDEX / MATCH function that make it superior to VLOOKUP. You will find a number of formula examples t...
When checking if a value exists in a range, using COUNTIF is quicker, easier and less prone to errors. This table shows the returning values for each formula...
Sample Data Table of Sales
Here is a method to repeat the values in Column ‘A’, (n) number of times required in Column ‘B’ outputting the results in Column ‘C’. The Formula is an Array...
Here’s a simple way to get the row number of the table items. It’s a simple formula referencing the current cell’s row minus the row number of the table head...
What is an Excel table?
Have you ever been in a VLOOKUP hell?
Delete the selected row in the active table will prompt the user that the selected cell in a table is about to be deleted and to Yes to confirm as deleting w...
The INDIRECT function returns a reference to a range. You can use this function to create a reference that won’t change if row or columns are inserted in the...
Add a row at the bottom of the active table. This will test that the active cell is in a table, then add a single row to at the bottom.
Loop through all tables in the workbook
This will show how to use VBA to paste values in Excel instead of formulas. You can bind this VBA macro to the Ctrl+Shift+V keyboard shortcut to make pasting...
This uses the StartImportCSV module to call the ImportCSVFile function, with set parameters to import a csv file. You can muck around with this and use other...
Pull data from a Closed Workbook Pass the Filepath, Filename, Sheet Name, Range and the Sheet that you want to pull the Data into. Call the Function, the Ran...
Here are some basic methods to get a Column Letter & Row Number from both parts of a Range ie. “A1:B2”. The examples below will result in Column “A”, Row...
Manually typing each option for a drop-down list in word (drop-down list content control) is time consuming, but there is no native way to import a list you’...
This will copy formulas from one place to another and then Copy & Paste the calculated results as Special Values There are 2 components, one is the long ...
Copies only the visible cells in a selected range to a new workbook, a A1
Copy Formula down a dynamic Column Range with or without AutoFill or using FillDown You can use the following methods to Copy Formula down a dynamic Column R...
Good if you are running a macro that will affect cell values
An easy user defined function (UDF) to get the nth word from cell. I often use this to get first and last names when the full name is in a single cell.
Function to test if the workbook is protected.
Function to tests if the active sheet is protected.
Tests if any sheets in the active workbook are protected with or without a password.
Function to test an entire column or row has been selected.
User Defined Function to get the unique count in a range.
Function to get the selected sheet names with examples.
Function to check if multiple named ranges exist
Obtaining Row information is easy since Rows are always Numbers. Column Letters that can be used in a Range are a little more tricky. I have many methods to ...
Here’s the simple steps to highlight the row and column of the selected cell which can be extremely useful when navigating large sets of data. Here’s a littl...
So you have a 0% value on one of your data labels and want to hide it?
What Are Custom Number Formats?
Use Conditional Formatting from the Format menu to apply a format to the cells. The formula used in the Conditional Formatting rule is based solely on the ro...
When you want to just show numbers as 9.1 M or 830 K
Example of Page Setup for the active sheet for printing example has a header, footer, narrow margins and the Column width to the page but not the rows.
Loop through each sheet in all open workbooks and remove the page break lines
InputBox Type 4 - get a boolean response
InputBox Type 0 - get formula from range
InputBox - Assign an array of Values to a Range
InputBox Type 8 - show how to get the selected range address
InputBox Type 8 - select a range and write values to each cell ```vb ‘======================================================================================...
InputBox Type 2 - get some text from the user
InputBox Example 1: (Type 1) - Inputbox to get a number from the user.
How it Works Using SEARCH and ISNUMBER When you need to check if a cell contains specific text (or string) we need to combing the SEARCH and ISNUMBER functio...
This method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.
There’s no built in way to count the number of words in Excel, but using the following formulas will get it done.
If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 ...
How to split first and last name from full name with space
Function to test if the workbook is protected.
Function to tests if the active sheet is protected.
Tests if any sheets in the active workbook are protected with or without a password.
Function to test an entire column or row has been selected.
Function to get the selected sheet names with examples.
Force the userform to load to the centre of the active excel window, add this code to the userform Initialize event. Perfect for when you have more than 1 ac...
A technique to add a custom right click menu (context menu) to a text box on a UserForm. There is no system context menu for text boxes in a userform, and th...
Snippets to loop through control types or a specific control type. Especially useful for bulk actions.
Easily add a custom right click menu for both normal ranges and in tables! First we need to put two codes into the ThisWorkbook Module, these 2 codes call ma...
A GUID (or UUID) is an acronym for ‘Globally Unique Identifier’ (or ‘Universally Unique Identifier’). It is a 128-bit integer number used to identify resourc...
User Defined Function to get the unique count in a range.
A GUID (or UUID) is an acronym for ‘Globally Unique Identifier’ (or ‘Universally Unique Identifier’). It is a 128-bit integer number used to identify resourc...
Link to all xlsx Example Files: https://github.com/ExcelTitan/Excel_Formulas
The INDIRECT function returns a reference to a range. You can use this function to create a reference that won’t change if row or columns are inserted in the...
If we had to calculate Total Sales the normal way, we would have to create a ‘helper column’ for the Totals column and then enter a formula to Sum all the To...
If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use ...
Summary SUMPRODUCT is an incredibly versatile function that can be used to sum and count like SUMIFS or COUNTIFS, but with more flexibility.
If we had to calculate Total Sales the normal way, we would have to create a ‘helper column’ for the Totals column and then enter a formula to Sum all the To...
We can expand on the Index and Match formula and use an array formula to look up a value based on multiple criteria. This is an array formula so need to pre...
Link to all xlsx Example Files: https://github.com/ExcelTitan/Excel_Formulas
Learn how to calculate commissions for a basic tiered plan and rate table using the VLOOKUP function.
There are 2 formulas to calculate percentage variance (change).
If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use ...
Download the example workbook here: calculate-time-between-dates.xlsx
These formulas are great for when you have to find the week ending date or even the fortnight ending date for a specific item. Week ending dates don’t have a...
When converting a decimal to hours and minutes, ensure to use the correct Number Format.
Delete the selected row in the active table will prompt the user that the selected cell in a table is about to be deleted and to Yes to confirm as deleting w...
Here I outline lots of ways that you can delete blanks rows in any chosen column.
These piece of code lets you delete an entire row within the used range of the sheet if the entire row contains no data.
Examples on how to first the last row, first available blank row or the last blank row
Copy Formula down a dynamic Column Range with or without AutoFill or using FillDown You can use the following methods to Copy Formula down a dynamic Column R...
Hyperlink a file or folder using the HYPERLINK formula in the active cell using VBA. This method pulls up a file picking dialog to choose a file or folder (...
This method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.
Forces the SaveAs UI to only accept a XLSM Macro-Enabled Workbook.
User can save the new workbook as file types: xlsx, xlsm, xlsb, xls, csv and txt with the following features: external links are broken formulas that re...
This joins many addresses together as a string, picks up a defined named range “sNamedRange” and clears the contents from the cells.
The following macro will hide all the column containing the word “hide” in each cell in row 1. Here is a brief description of how the code works:
Here I outline lots of ways that you can delete blanks rows in any chosen column.
Example of Page Setup for the active sheet for printing example has a header, footer, narrow margins and the Column width to the page but not the rows.