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...
Posts by Tag
- developer 13
- text-strings 12
- practical 10
- lookup-reference 10
- tables 9
- copy-data 9
- function 9
- interface-formatting 7
- inputbox 7
- text 5
- validation 5
- userform 4
- unique 4
- summing-counting 4
- array-formulas 3
- accounting 3
- date-time 3
- deleting-data 3
- dynamic-ranges 2
- files 2
- export 2
- deleting 1
- practical-applications 1
- Dynamic Ranges 1
- Printing 1
- sharepoint 1
developer
Function To Test If The Workbook Is Protected
Function to test if the workbook is protected.
Function To Test If The Active Sheet Is Protected
Function to tests if the active sheet is protected.
Function To Test If Any Sheets Are 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 test an entire column or row has been selected.
Function To Get The Selected Sheet Names With Examples
Function to get the selected sheet names with examples.
Function Check Multiple Named Ranges Exist
Function to check if multiple named ranges exist
Examples Of Vba Constants
Some examples of VBA constants, useful when developing programs / add-ins.
Backup And Copy The Active Sheet
Good if you are running a macro that will affect cell values
32 Bit And 64 Bit Api Declarations For Vba Developers
A whole heap of declarations for 32 and 64 bit operating systems.
Prevent Vba Code From Running While In Read Only Mode
Sub ReadOnlyCheck() Dim Answer As Long 'Check to see if file is Read-Only If ThisWorkbook.ReadOnly = TRUE Then Answer = MsgBox("Someone Is curren...
Exit Sub If The Selection Is Not A Range Of Cells
This code snippet is useful to check if the selection is a range of cells.
Custom Right Click Menu (context Menu)
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...
text-strings
Get The Nth Word From A Text String with vba
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.
Generate A Guid To A Selected Range Of Cells With Vba
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...
Function To Get The Unique Count In A Range
User Defined Function to get the unique count in a range.
Convert Numbers Stored As Text When Using A Formula
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 ...
Formula To Get File And Folder Names
Let’s image that the following path is in A1: C:\Users\foo\Documents\parent.folder\foo.bar.txt
Split And Join Substitute And Perform Surgery On Text Strings
Convert text to lower case Example: =LOWER("hello") Result: hello
Special Characters And Symbols
You can always use the Insert –> Symbols method to enter characters onto your spreadsheet but you can also add them via a formula.
Get The Workbook And Sheet Name With A Formula
These formulas require that the workbook is saved
Clean And Trim Data With These Functions
Combine these 3 functions to completely clean your data: =TRIM(text) =CLEAN(text) =SUBSTITUTE(text,” “,””)
Clean Data With Vba
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...
Add Leading Or Trailing Text To Constant Cells In A Range
You will be prompted with an input-box for the range of cells and for the leading text.
Use Vba To Join Cell Values With Either & Or Concatenate Formula
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.
practical
Repeat Values N Times
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...
Paste Values With Vba With A Keyboard Shortcut Permalink
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...
Get A List From Excel And Populate Into A Drop Down List Control In Word
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’...
Format Numbers As Thousands And Millions
When you want to just show numbers as 9.1 M or 830 K
Create Sheets From Cell Values
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.
Create A Named Range And Choose The Scope
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...
Converting Column Numbers To Letters
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 ...
Clean Data With Vba
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...
Create A Table Of Contents
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...
Remove Page Break Lines In All Workbooks
Loop through each sheet in all open workbooks and remove the page break lines
lookup-reference
Get The Week Or Fortnight Ending Dates With A Formula
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...
Indirect Sheet Referencing To Calculate Ranges
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...
Top Values In A List
From a list of 30 people with an assigned value, generate a smaller list that are the top n values in that list.
Calculate Sales Commission With Vlookup Sale Amount Tiers
Learn how to calculate commissions for a basic tiered plan and rate table using the VLOOKUP function.
Formula To Get File And Folder Names
Let’s image that the following path is in A1: C:\Users\foo\Documents\parent.folder\foo.bar.txt
Multiple Criteria Lookup With Index And Match
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...
Get The Workbook And Sheet Name With A Formula
These formulas require that the workbook is saved
Generate A List Based On Criteria
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...
Index And Match
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...
Check Value Exists With Countif
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...
tables
Sample Data Table
Sample Data Table of Sales
Repeat Values N Times
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...
Put A Row Number On A Table
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...
Getting Started With Excel Tables
What is an Excel table?
Introduction To The Excel Data Model & Relationships
Have you ever been in a VLOOKUP hell?
Delete The Selected Row In The Active Table
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...
Indirect Sheet Referencing To Calculate Ranges
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 To The Bottom Of The Active Table
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 Tables In The Workbook Or Active Sheet
Loop through all tables in the workbook
copy-data
Paste Values With Vba With A Keyboard Shortcut Permalink
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...
Import Csv To Excel With Paramaters
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...
Get Data From A Closed Workbook As Special Values Very Fast
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...
Get Column Letter And Row Number From Both Parts Of A Range
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...
Get A List From Excel And Populate Into A Drop Down List Control In Word
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’...
Copying Data Worksheet To Worksheet
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 ...
Copy Visible Range To A New Workbook
Copies only the visible cells in a selected range to a new workbook, a A1
Copy A Formula Down A Dynamic Column Range
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...
Backup And Copy The Active Sheet
Good if you are running a macro that will affect cell values
function
Get The Nth Word From A Text String with vba
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 test if the workbook is protected.
Function To Test If The Active Sheet Is Protected
Function to tests if the active sheet is protected.
Function To Test If Any Sheets Are 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 test an entire column or row has been selected.
Function To Get The Unique Count In A Range
User Defined Function to get the unique count in a range.
Function To Get The Selected Sheet Names With Examples
Function to get the selected sheet names with examples.
Function Check Multiple Named Ranges Exist
Function to check if multiple named ranges exist
Converting Column Numbers To Letters
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 ...
interface-formatting
Highlight The Row And Column Of The Selected Cell
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...
Hide Zero Values In Data Labels
So you have a 0% value on one of your data labels and want to hide it?
Custom Number Formats
What Are Custom Number Formats?
Colour Banding With Conditional Formatting
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...
Format Numbers As Thousands And Millions
When you want to just show numbers as 9.1 M or 830 K
Example Of Print Setup For The Active Sheet
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.
Remove Page Break Lines In All Workbooks
Loop through each sheet in all open workbooks and remove the page break lines
inputbox
Inputbox Type 4 Get A Boolean Response
InputBox Type 4 - get a boolean response
Inputbox Type 0 Get Formula From Range
InputBox Type 0 - get formula from range
Inputbox Assign An Array Of Values To A Range
InputBox - Assign an array of Values to a Range
Inputbox Type 8 Show How To Get The Selected Range Address
InputBox Type 8 - show how to get the selected range address
Inputbox Type 8 Select A Range And Write Values To Each Cell
InputBox Type 8 - select a range and write values to each cell ```vb ‘======================================================================================...
Inputbox Type 2 Get Some Text From The User
InputBox Type 2 - get some text from the user
Inputbox Type 1 Get A Number From The User
InputBox Example 1: (Type 1) - Inputbox to get a number from the user.
text
Check If A Cell Contains Specific Text
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...
Extract Link Address From (insert)hyperlink Or Hyperlink Formula
This method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.
Word Count From Cells
There’s no built in way to count the number of words in Excel, but using the following formulas will get it done.
Extract Nth Word From A String
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 ...
Split The First And Last Name With A Formula
How to split first and last name from full name with space
validation
Function To Test If The Workbook Is Protected
Function to test if the workbook is protected.
Function To Test If The Active Sheet Is Protected
Function to tests if the active sheet is protected.
Function To Test If Any Sheets Are 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 test an entire column or row has been selected.
Function To Get The Selected Sheet Names With Examples
Function to get the selected sheet names with examples.
userform
Centre Userform For Dual Or Multiple Displays
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...
Adding A Context Menu To A Userform’s Textbox
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...
Loop Through Control Types In A Userform
Snippets to loop through control types or a specific control type. Especially useful for bulk actions.
Custom Right Click Menu (context Menu)
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...
unique
Generate A Guid To A Selected Range Of Cells With Vba
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...
Function To Get The Unique Count In A Range
User Defined Function to get the unique count in a range.
Formula To Create A Guid
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...
Count Unique Values In A Range
Link to all xlsx Example Files: https://github.com/ExcelTitan/Excel_Formulas
summing-counting
Indirect Sheet Referencing To Calculate Ranges
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...
Calculate Total Sales With An Array Formula
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...
Calculate A Running Total Of A Column Of Cells Ie Cumulative Total
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 ...
Sumproduct To Sum Or Count Across Multiple Columns
Summary SUMPRODUCT is an incredibly versatile function that can be used to sum and count like SUMIFS or COUNTIFS, but with more flexibility.
array-formulas
Calculate Total Sales With An Array Formula
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...
Multiple Criteria Lookup With Index And Match
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...
Count Unique Values In A Range
Link to all xlsx Example Files: https://github.com/ExcelTitan/Excel_Formulas
accounting
Calculate Sales Commission With Vlookup Sale Amount Tiers
Learn how to calculate commissions for a basic tiered plan and rate table using the VLOOKUP function.
Percentage Variance With Positive And Negative Values
There are 2 formulas to calculate percentage variance (change).
Calculate A Running Total Of A Column Of Cells Ie Cumulative Total
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 ...
date-time
Calculate Time Between Dates
Download the example workbook here: calculate-time-between-dates.xlsx
Get The Week Or Fortnight Ending Dates With A Formula
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...
Convert Decimal To Total Hours And Minutes
When converting a decimal to hours and minutes, ensure to use the correct Number Format.
deleting-data
Delete The Selected Row In The Active Table
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...
Delete Rows In A Column That Are Blank
Here I outline lots of ways that you can delete blanks rows in any chosen column.
Delete Entire Row If No Data Is Found
These piece of code lets you delete an entire row within the used range of the sheet if the entire row contains no data.
dynamic-ranges
Finding The Last Row Or First And Last Available Blank Row
Examples on how to first the last row, first available blank row or the last blank row
Copy A Formula Down A Dynamic Column Range
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...
files
Hyperlink Using The Hyperlink Formula With Vba
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 (...
Extract Link Address From (insert)hyperlink Or Hyperlink Formula
This method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.
export
Force Saveas Ui To Only Accept Macro Enabled Workbook (xlsm)
Forces the SaveAs UI to only accept a XLSM Macro-Enabled Workbook.
Export And Save Selected Sheets To A New 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...
deleting
Clear Multiple Cells Or Ranges On A Worksheet
This joins many addresses together as a string, picks up a defined named range “sNamedRange” and clears the contents from the cells.
practical-applications
Hide All Columns That Contain A Value In A Cell
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:
Dynamic Ranges
Delete Rows In A Column That Are Blank
Here I outline lots of ways that you can delete blanks rows in any chosen column.
Printing
Example Of Print Setup For The Active Sheet
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.