# Tag Archives: excel

## 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: =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:

 Source 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```

## 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 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.

## [Excel] Vlookup in VBA

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)`

## 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 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.

## [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, 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

## 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 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

 Source code
```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 Function```

Now let’s break it down.

First you need to declare your regEx obejct with:

 Source code
`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.

 Source code
```regEx.Pattern = "\b\w{5}\b"
regEx.IgnoreCase = True 'True to ignore case
regEx.Global = True 'True matches all occurances, False matches the first occurance```

Now to test if the pattern has been found in a string you can use

 Source code
`regEx.Test(sentence)`

To get an array of matches from your regex you simply have the regex execute.

 Source code
`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:

 Source code
```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
Next```

Here’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”)

## 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 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)