Category Archives: Excel
Posted by admin on September 20, 2014
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 pulled up, you would use sumproduct([Range of Hours], [Range of Pay Rate]).
This formula is even more powerful as it can also handle division. This is why there is no analogous divideproduct formula, because you can do it all with sumproduct!
The trick is to put 1/ in front of the range that will be your divisor (aka denominator). Here is a screenshot with an example:
In essence, for each value in range D3:D6, Excel is transforming it into 1/D3 => 1/70, 1/D4 =>1/130, 1/D5 => 1/105, 1/D6 => 1/100 before performing the sumproduct between all 3 ranges.
There are a lot of situations you might need to use this where you are using rates, adjustment values, discounts, or percentages. In the example above, I basically needed to adjust for the cost of living in different areas using rates to see the differences in cost of living if I were to move to different cities. Basically, I needed to calculate a rate for each spending category to figure out the relative cost of living adjustment for each category. I could have added another column to do that calculation and used that for a sumproduct, but this method results in a much cleaner looking spreadsheet.
Note this works in Google Doc’s version of sumproduct as well! If other office programs don’t support the 1/[array] syntax, you’ll have to make an additional column to do the division and then use that new column in your sum product.
Here’s the demo spreadsheet that you can download and use to play around with:
Posted by admin on February 18, 2014
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: =TEXT([Number to add trailing 0’s],”Put a 0 to match the desired length”)
So if you’re trying to make “1” six digits long, you would put six zeros in a row like: “000000”.
If you’re trying to do the same thing on an alphanumeric string, try adding this custom formula into your workbook. After you paste the code into a VBA module, you can call it by typing addZeros([the Alphanumeric string], [desired length of string])
So for example: =ADDZEROS(“AA”,4) will result in 00AA
Here’s the code:
Public Function addZeros(makeLonger As String, numberDigits) As String
Dim x
'Adds 0's to the front of the number until it's the correct length
'Change "0" to another character if you need a different character than 0
For x = 1 To (numberDigits - Len(makeLonger))
makeLonger = "0" & makeLonger
Next x
addZeros = makeLonger
End Function
Posted by admin on November 5, 2011
So say you want to select a couple columns through a loop and you need to be able to iterate through columns numerically; so you would need to select a range numerically.
Here’s the simple way:
Columns(“A:B”).Select
Range(Columns(1), Columns(2)).Select
Posted by admin on November 5, 2011
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 Save and Close VBA macro in to a new module
Sub SaveAndClose()
ActiveWorkbook.Close SaveChanges:=True
End Sub
Step 2: Make whatever changes you want to the worksheets / workbook.
Step 3: Run the macro and it will save the changes that you made and close the work book.
Posted by admin on November 5, 2011
Using a Vlookup in VBA for your macros isn’t as straight forward as you would think. The trick is that Vlookup is a worksheet function that you need to reference using the Application variable in VBA, when you code your macro.
Note: If you’re looking for something similar for Access VBA, it’s called Dlookup.
There are a couple ways of calling up a vlookup in a macro, both should work.
Application.VLookup(Range("A3"), Range("A3:B9"), 2, False)
Or
Application.WorksheetFunction.VLookup(Range("A3"), Range("A3:B9"), 2, False)
Posted by admin on November 5, 2011
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 the passwords, just like I do. So if you need to unlock the password protection on a worksheet here’s some quick and easy instructions on using a macro to help you unprotect the sheet.
Please don’t use these steps for unlawful purposes. Also these steps will only work for password protected sheets, which are the ones you can’t edit but you can still see the data on them.
For advanced users you can skip straight to the code on step 3.
Step 1: Press ALT + F11 on your keyboard to open up the Visual Basic Editor
Step 2: Click on [Insert] then [Module]
Step 3: Copy & Paste this code into the module window that pops up:
Sub PasswordBreaker()
'Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
Just like this:
Step 4: Go back to the password protected sheet and press ALT + F8
Step 5: Select the PasswordBreaker macro and click run. It takes a little while to run, but it should do the trick!
Posted by admin on October 9, 2011
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 have to do is open Excel from your start menu each time you want an independent instance of Excel to run, and then open the worksheet in your new program window.
Volia! Two ribbons, two spreadsheets, and you can drag them around to two different monitors if you please. They can finally be two different sizes, and whatever else you need them to do. One minor downside though, is that referencing cells between the 2 instances of Excel doesn’t work.
Posted by admin on October 9, 2011
| 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, start, length) |
Extract the middle of a string |
Mid(“Hello”,2,2) |
el |
| Instr(start, look in this string, find this string) |
Find if a string is in another string |
Instr(0,”Hello there”,”Hello”) |
1 |
| Trim(str) |
Removes preceding and trailing spaces |
Trim(” hello there “) |
hello there |
| LTrim(str) |
Removes spaces from the beginning of a string |
Ltrim(” hello “) |
hello |
| Rtrim(str) |
Removes spaces from the end of a string |
Rtrim(” hello “) |
hello |
| String(num, char) |
Repeats the char num times |
String(2, “a”) |
aa |
| Space(num) |
Makes a certain num of spaces |
Space(2) |
|
| Cint(str) |
Change a string to a integer |
Cint(111.1) |
111 |
| Clng(str) |
Change a string to a long (use if you get an overflow error) |
Clng(111.1) |
111 |
| Cdbl(str) |
Change a string to a double |
Cdbl(111.1) |
111.1 |
| Split(str,delimiter) |
Split a string into an array |
Split(“Hello,There,Friend”, “,”) |
(0) = Hello
(1) = There
(2) = Friend |
| Join(array, delimiter) |
Join an array into a string |
Join(array(“Hello”, “There”, “Friend”),”,”) |
Hello,There,Friend |
Posted by admin on October 8, 2011
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 data mine. The directions are the same for Microsoft Access as well.
First thing first, let’s enable the reference in Excel so that we can use this functionality.
Go to Tools > References
Then find Microsoft VBScript Regular Expressions 5.5
Click Ok, and now you’ll be able to create a RegExp object to use in your VBA.
Here’s a basic function that finds 5 letter words in a string.
Here’s a copy of the Excel file that I am using in the example: RegEx Excel Tutorial XLS File
Function findFiveLetter(sentence)
Dim regEx As New VBScript_RegExp_55.RegExp
Dim matches, s
regEx.Pattern = "\W\w{5}\W"
regEx.IgnoreCase = True 'True to ignore case
regEx.Global = True 'True matches all occurances, False matches the first occurance
s = ""
If regEx.Test(sentence) Then
Set matches = regEx.Execute(sentence)
For Each Match In matches
s = s & " Position: " & Match.FirstIndex
s = s & " Word: " & Match.Value & " "
s = s & Chr(10)
Next
findFiveLetter = s
Else
findFiveLetter = ""
End If
End FunctionNow let’s break it down.
First you need to declare your regEx obejct with:
Dim regEx As New VBScript_RegExp_55.RegExp Next you give your object some parameters.
The most important is .Pattern, which is the pattern of characters that you want your function to find. Then there are a couple of settings like Global, if you want it to search for all occurrences (TRUE) or just the first occurrence (FALSE), and also IgnoreCase.
Here’s a simple guide to get you started on writing regex patterns.
regEx.Pattern = "\b\w{5}\b"
regEx.IgnoreCase = True 'True to ignore case
regEx.Global = True 'True matches all occurances, False matches the first occuranceNow to test if the pattern has been found in a string you can use
To get an array of matches from your regex you simply have the regex execute.
Set matches = regEx.Execute(sentence) To get the results from this array object, you can reference matches(n) or use a For Each loop. There are two important pieces of information returned in the array, the index of the match and the actual match.
I used a For Each:
For Each Match In matches
s = s & " Position: " & Match.FirstIndex 'Position of the match
s = s & " Word: " & Match.Value 'The actual match
s = s & Chr(10) 'Prints a new line
NextHere’s the example output:
| Line |
Matches |
| What began as a small group of protesters expressing their grievances |
Position: 5 Word: began
Position: 16 Word: small
Position: 22 Word: group
Position: 53 Word: their |
| about economic inequities last month from a park in New York City |
Position: 0 Word: about
Position: 31 Word: month |
| has evolved into an online conversation that is spreading across the harry |
Position: 69 Word: harry |
| country on social media platforms. |
Position: 18 Word: media |
Here’s a copy of the Excel file that I used in the example: RegEx Excel Tutorial XLS File
The regex function is called findFiveLetter(“put a string in here”)
Posted by admin on October 8, 2011
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 in regex patterns, so you need to type a \ in front of them if you want to specifically match them in the pattern.
Common special characters are: + . \ – [] {} $ ? |
Example: If I wanted to match taste, taster, tasters, I would type a pattern like taste\w{0,2}
If I wanted to match taste!, I would write a pattern like taste\!
| Code |
What does it Match? |
| \d |
Numbers |
| \w |
Word characters like letters of any case |
| \s |
Spaces |
| \b |
Boundary of a word character |
| \D |
Not a number |
| \W |
Not a word character |
| \S |
Not a space |
| \n |
Line break |
| \r |
Line break |
| \t |
Tab |
| ? |
Previous code is optional |
| * |
Repeat previous code 0 to infinity |
| + |
Repeat previous code once or more |
| {1} |
Repeat previous code once (can use any number) |
| {1,2} |
Repeat previous code 1 to 2 times (can use any numbers) |
|
