Excel's hidden word proccessing power otherwise known as VBA
Okay time to reach deep down and pull out a miracle. Have you ever wanted to format some text in an excel document? Well with that said anybody who has tried knows that it isn't an easy task. That is unless you happen to do a little VBA code in the background. The problem with Excel, and it's not so much a problem as it is designed that way, is that it doesn't offer much when you need to modify something with a letter or non numeric value in it. This requires you to write some code to perform some good old fashioned string parsing. For those of you who have never worked with VBA don't worry it doesn't bite, it is actually very simple. All of the functions to read, write, and look through your string are built into VBA. With that said let's fire up excel and head into town.
The example I am going to demonstrate here is based off of a simple string needing to be turned into a MAC address like format. In this case I like the CISCO xxxx.xxxx.xxxx format since that's what I am going to be wondering anyways when I look at the spreadsheet. So first off we make a few handy columns on the spreadsheet as shown to the side. Note that I am using the working version so some of the mac address's are already showing up how they should in the end. Once you've got the basic spreadsheet created right click on the name of the worksheet and select view code. For those now going what in the world is this. This is your new friend who has always been there you just didn't know it. Microsoft for the longest time has built in the VBA editor to the office applications. However, if you haven't had to ever add create a custom function or tweak the worksheet to do what you want you have never had the joy of working with this editor. Just like most of the ed
itors it has intellisense built so that when you type a keyword in you well be greeted with it standing out from the rest of your code. On the sheet that is now open type the code to the side. The basic principal is that we are calling a function that happens every time the worksheet is updated. This function gets passed the object, in this case Target, as a range. For those not familiar a range can contain several different items and essentially holds all of the information excel knows about your individual cells. Notice the first If statement checks to make sure the change happened in column 4. Let me repeat that it says column 4. When you look back at your worksheet you will notice it displays the columns with labels that use letters not numbers. Pull out your toes and starting from A which will be one count over to the Mac Address column. Notice that it's 4. If you have to do this on a different column or even a specific range remember this as you will change the value accordingly. Now that we know we have the right column let's set our Range to a variable so we can do some manipulation. the first of these manipulations is to make sure that whatever we just put in the column is the right length, in this case that would be 12 characters. To do this we use the LEN function which returns the length of the string in a numeric value that we can compare.So now we have confirmed that it's in column 4 and it is the right length and need to format it. With that said we create a variable called newmac to hold the contents of our pieced together string. Notice That I did this in one line of code spread over several lines for ease of reading but you could also do several iterations of newmac = ..... The big items to notice in this listing are the string manipulation functions. First there is LEFT, which is given in the form LEFT(string,startat,length) this is fairly straight forward to understand in that you tell it I want to take this "string" starting at x characters from the LEFT and end at y characters. this gives us the first 4 characters of our new string which we than use the ampersand sign "&" to tell it to concatenate the string with the next item which is the ".". That done we move on to the next function MID. MID allows for us to specify the start character anywhere in a string and parse the output to the length specified. in this case we used our string and started at the 5th character and ended at the 9th character by using 4 for the length. Now let's slap a "." in there and move on to the last statement which is RIGHT(mymac,4) This as you are thinking does the exact opposite of the LEFT and says read me the last 4 characters of the string. Wonderful now we have our string built. Not only did we manage to build our string but we also test to make sure it's 12 characters long and it's in the right column. Why is that important might you ask? Let's follow the magic trolley and imagine if you didn't specify the column. Oh no that's the most horrible train wreck I've seen. Oh sorry got caught up there for a second. If you don't check for the column or even in this case the length being 12 you end up with your new friend being called every single time you update a cell. This in turn leads to calls, and errors, and disgruntled workers. As a general practice you should always make sure you have what you want to run your code against defined 100% clear. It would be like saying I want a car and when you get a matchbox car being surprised. Now for the last magical item, setting the value. Without the last step you have a great hot rod but no engine. This statement sets the string we just worked so hard to create to the Target "object" and another mac address is born. So next time you need to format a serial number full of numbers and letters remember this little trick and everything will be alright.
itors it has intellisense built so that when you type a keyword in you well be greeted with it standing out from the rest of your code. On the sheet that is now open type the code to the side. The basic principal is that we are calling a function that happens every time the worksheet is updated. This function gets passed the object, in this case Target, as a range. For those not familiar a range can contain several different items and essentially holds all of the information excel knows about your individual cells. Notice the first If statement checks to make sure the change happened in column 4. Let me repeat that it says column 4. When you look back at your worksheet you will notice it displays the columns with labels that use letters not numbers. Pull out your toes and starting from A which will be one count over to the Mac Address column. Notice that it's 4. If you have to do this on a different column or even a specific range remember this as you will change the value accordingly. Now that we know we have the right column let's set our Range to a variable so we can do some manipulation. the first of these manipulations is to make sure that whatever we just put in the column is the right length, in this case that would be 12 characters. To do this we use the LEN function which returns the length of the string in a numeric value that we can compare.So now we have confirmed that it's in column 4 and it is the right length and need to format it. With that said we create a variable called newmac to hold the contents of our pieced together string. Notice That I did this in one line of code spread over several lines for ease of reading but you could also do several iterations of newmac = ..... The big items to notice in this listing are the string manipulation functions. First there is LEFT, which is given in the form LEFT(string,startat,length) this is fairly straight forward to understand in that you tell it I want to take this "string" starting at x characters from the LEFT and end at y characters. this gives us the first 4 characters of our new string which we than use the ampersand sign "&" to tell it to concatenate the string with the next item which is the ".". That done we move on to the next function MID. MID allows for us to specify the start character anywhere in a string and parse the output to the length specified. in this case we used our string and started at the 5th character and ended at the 9th character by using 4 for the length. Now let's slap a "." in there and move on to the last statement which is RIGHT(mymac,4) This as you are thinking does the exact opposite of the LEFT and says read me the last 4 characters of the string. Wonderful now we have our string built. Not only did we manage to build our string but we also test to make sure it's 12 characters long and it's in the right column. Why is that important might you ask? Let's follow the magic trolley and imagine if you didn't specify the column. Oh no that's the most horrible train wreck I've seen. Oh sorry got caught up there for a second. If you don't check for the column or even in this case the length being 12 you end up with your new friend being called every single time you update a cell. This in turn leads to calls, and errors, and disgruntled workers. As a general practice you should always make sure you have what you want to run your code against defined 100% clear. It would be like saying I want a car and when you get a matchbox car being surprised. Now for the last magical item, setting the value. Without the last step you have a great hot rod but no engine. This statement sets the string we just worked so hard to create to the Target "object" and another mac address is born. So next time you need to format a serial number full of numbers and letters remember this little trick and everything will be alright.
Comments
Post a Comment