How to Use Sumproduct to Divide instead of Multiply

Sumproduct is powerful Excel formula to multiply 2 or more paired ranges (aka arrays or lists) together and sum the product.  This is most commonly used in areas like payroll where you have a list of people, their hours worked, and their pay rate.  If you wanted to get the total payroll for the period you have … Continue reading “How to Use Sumproduct to Divide instead of Multiply”

Formula to Add Leading Zeros in Excel

To make a set of numbers of varying length into strings with the same number of characters, try using the TEXT formula.  This formula won’t handle strings, so if you need to add leading zeros to an alphanumeric identifier you might have to try modifying the VBA formula pasted at the end. Here’s an example: … Continue reading “Formula to Add Leading Zeros in Excel”

Excel – Close and Save active workbook in VBA with no Prompt

This VBA code will save an Excel workbook file and close it when it runs.  You could put a button or some other trigger on your worksheets to initiate the macro so that it’s easier for your users to access the macro. Instructions to Install VBA to Save Workbook with No Prompt Step 1: Copy paste this … Continue reading “Excel – Close and Save active workbook in VBA with no Prompt”

[Excel] How to ulock a protected Excel Worksheet, Step by Step

If you’re like me, you might have a tendency to forget passwords. I always have good intentions on trying to keep workbooks secure and the formatting and data stable, and sometimes those efforts fall flat when I forget the password. Or you might have gotten the worksheets from someone else and they’re might have forgotten … Continue reading “[Excel] How to ulock a protected Excel Worksheet, Step by Step”

Have two Excel Workbooks Open at the Same Time

I know that this is one of my pet peeves. If you open multiple workbooks in Excel, they all get stuck together in the same window, and it’s hard to do split screens or multiple monitors. Thankfully there’s an easy way to get two (or more) independent windows of Microsoft Excel to open. All you … Continue reading “Have two Excel Workbooks Open at the Same Time”

[Cheatsheet] VBA String Functions

Function What Does it do? Example Result Len(str) Returns the length of the string Len(“Hello”) 5 Ucase(str) Changes the string to upper case Ucase(“Hello”) HELLO Lcase(str) Changes the string to lower case Lcase(“Hello”) hello Left(str, num) Shorten the string from the left Left(“Hello”,3) Hel Right(str, num) Shorten the string from the right Right(“Hello”,3) llo Mid(str, … Continue reading “[Cheatsheet] VBA String Functions”

Simple Regular Expression Tutorial for Excel VBA

I’ll go through a simple tutorial for using regular expressions for VBA.  This is a great quick start guide for people who have used regular expressions before in other languages.  Just as a review, regular expressions are used to match patterns in strings.  This is a powerful tool that can help you data cleanse and … Continue reading “Simple Regular Expression Tutorial for Excel VBA”

Basic Regular Expression Patterns for Beginners

This is super simplified, but it’s enough to get started with. I remember being confused by the gigantic tables with all the special clauses, so when you’re beginning keep it simple. Remember if you want to match a literal character you can just type it out, unless there’s a special character. Special characters have meanings … Continue reading “Basic Regular Expression Patterns for Beginners”