Excel VBA Introduction Part 53.1 - Working with Strings

welcome to this wise L excel vba tutorial this video is going to give you a brief introduction to working with strings in VBA which is something you probably don't count less times already they've got to this point in this tutorial series this video is designed to give you a little bit more of a background about some of the specifics of working with strings so going to start by talking about how to declare string variables using a couple of different styles of declaration and then we'll talk about fixed length and variable length strings and what the difference is and why both are useful will explain how you can convert values of different types into strings which in VBA most cases happens implicitly anyway it isn't not much you need to do that we'll also talk about how you can deal with nulls which is one thing that you can't convert into a string we'll spend a little time talking about concatenation and talk about two different operators you can use to concatenate strings in VBA we'll also talk about string constants things like special characters like tab characters and newline characters and give you an explanation as to why there are so many different types of newline constants sort of like new lines crlf CR and LF by themselves and explain the history of those and why they're there's way they are we'll talk a little bit to agenda Livity about comparing strings as well including how to deal with case sensitive strings and then the last part of the video is going to talk about using wildcards and pattern matching so you can do elaborate things like fine strings that start with or end with or contains or don't contain etc etc it's quite a lot to do here all those lots of the ideas are quite simple and you may have encountered them before but let's get stuck in okay so the starting point of this video is our fairly standard workbook full of data all done movies puts a link in the video description in case you want to download a copy of the exact same data that I'm working with or that's not absolutely necessary to work with the examples in this video that might take a couple of days of the link to appear as well so if you were desperate to get started and absolutely certain you are desperate to get started then we have used a very similar data set in a previous video and there's a link in the in the description of that video on our websites that you need to download the same data anyway once you have got a copy of the data we're going to head straight into the VBA editor so I had to the Developer tab of course and use Visual Basic or press alt + f11 insert a new module and then I'm going to start with a very simple subroutine called basic strings now this video is designed to try to give you a complete and understanding as possible of strings in VBA so although it's going to seem a little bit pattern izing at this point particularly if you have worked in VBA for quite a while we're going to set with the absolute basic principles of what strings actually are so in VBA string is just the name of the datatype for any value which contains text the most common reason you're likely to use the word string in your code is when you're declaring a variable to hold a string so for example you can say dim s as string you'll have done similar things to that across many different examples if you follow this video tutorial so far that variable now can accept any string value so it might be a literal string that you type in or it could be a string value of a property of an object for instance the name of the workbook or the address of a cell but commonly if you wanted to enter a literal string and store that in a variable you'd say F equals and then for literal literal string spec button you don't those in a set of double quotes and inside your double quote you pretty much I've been anything you like it doesn't really matter at this point nice treats it as a string of text there is a bit of a limit to how big a string you can hold it up to about 2.1 for 7 billion characters where it should be more than enough for any literal string you want the store and so you know that's a test that by typing into what mobile seven billion character that'd be a pretty boring video but if you've done that but you wanted to see that that value just gets stored in the variable simple way to do that is to view the locals window from the view menu so I choose view locals window then if I use the f8 key to step through this procedure by click anywhere in there use f8 step through you'll see that the value that you've entered gets stored in that variable as a string now there are also lots of methods and properties in VBA that return strings so for example the path property of the workbook object that returns a string that indicates the folder path in which the file is going to be stored so if I said s equals this workbook dot path one way to tell that this property does return a string is once you've typed the property in is to press ctrl + I while the cursor is in contact with the word path and little tooltip should pop up indicating that it does indeed return is value as a string so I can do that for the path of the workbook I could say something like s equals application version that returns the version number of the application but it's returned as a string again pressing ctrl + I indicates that it returns a string then I could say something like s equals let's see something like active sheet dot name so I know the name of the worksheet that I'm currently sitting on I could also say s equals active cell dot address so I know what cell reference I'm I'm currently sitting in if I press ctrl + I at the end of the address property that's a slightly more complex property it has a number of parameters but they're all optionals I don't need to pass anything into these parameters the important thing again is that returns a string at the end so if I were to just use the f8 key to step through this now you'll be able to see the value of your string variable changing as you go through so you can see that you may turn different strings and store those in a basic string variable now you don't actually have to be all that careful about what type of data you store in a string variable vba has a feed to refer to as implicit type conversion and all that means in simple terms is that if you have a variable with one data type and you attempt to store a value of a different data type in that variable if the type of the value can be converted into the type of a variable then Vijay will handle that for you automatically so just to demonstrate that really quickly let's have another couple of lines similar says s equals one two three so that's clearly a numeric value I could also say that equals then I'm going to enter a date value here so I'm gonna put in 1902 / 2017 which is today's date if I enter that into in hash marks then is treated as a date value it also flip around the other formatting so it's month/day/year in VBA which may tripped you up in the past if I were to then just comment out the other lines of code there and I can then use the f8 key to step through you'll see the value 1 2 3 gets inserted into a set of double quotes so that's clearly a literal string now rather than a numeric value and then the date so entering there that gets converted into a string as well you can see the 1 is converted into a string it's using the regional settings of my computer in 19 to such 2017 if you can just about make that out the same is true for properties and methods that return the strings or other data types if I just end that procedure and then let's say something like s equals find a property that returns a number so let's say active cell dot row so the row property doesn't return a string if I press ctrl + I it returns a value as the long type which is a long integer which is a number up to about 2.1 for seven billion i can do the same sort of thing for and let's go first something slightly different s equals this workbook dot sheets dot count account property always returns a numeric value if i press ctrl + i that returns its values along again i could also try to store the result of the date function in my string variables i can say s equals date which will try to calculate today's date and once again if I were to just comment out these two lines and then use f8 step-through you should see that we've got the value 1 stored for the row number of the active cell then again the number one stored for the number of sheets in the workbook and then today's date again stored as the string the value of today's date so basically just to demonstrate that you can effectively store pretty much any value you like in a string variable it doesn't matter whether it's a number or a date or a piece of text strings will pretty much happily hold them now although you can rely happily on implicit type conversion in most cases there are one or two situations where that falls over you'll see one other example a little later on in this video but just for now to demonstrate the principle of explicit type conversion every data type in VBA has a type conversion function so the one that will convert Bao these two strings explicitly in VBA is called C STR convert to string so just to demonstrate that very quickly we can say rather than just saying active cells are oh and relying on VBA to convert the long to a string we could say C stro cstr and open some parentheses and you'll see of course that the results of that expression the rules of that calculation would be to return its value as a string so if I wrap up the close parentheses at the end and I can do exactly the same thing for each of these other values so we'll take each's and numbers or dates and convert those into strings explicitly rather than implicitly and then return those or store those in my string variable so for this particular example I won't see any difference whatsoever in what happens I'll see two number one stored as a string and then the number one stored as a string and then today's date again stored as a string but as I say a little later on in the video you'll see why that can be sometimes a useful necessary function now there is one particular thing that you can't convert into a string in VBA either implicitly or explicitly and that thing is a null so just to demonstrate what I mean by that if I were to comment out these three lines of code and then attempt to store and know in my string variable s equals null now it's fairly pointless writing that instruction because that will never ever work if I just hit f5 to run the entire procedure your favor immediately with a runtime error of invalid use of null when it hits that line I can't do that even if I attempt to explicitly convert the null into a string if I use C stress you know that still doesn't work because you simply can't convert known into string values north indicate the absence of any valid data whatsoever and so if there it's not likely that you'll encounter it that often in Excel VBA it's more of a concept to databases so if you we're perhaps creating code that connected to a database and loaded query on that database to return some data some of the fields or columns in the database tables can contain nulls and if you attempted to store that the value of the field in a string variable and the field can say no then you'd run into problems so we've got a video actually they've explained how to connect to databases and query a database and return it back to an Excel workbook or some excel vba code so you're really really interested in that I'm not going to go into it in detail here because we've already created videos have explained all about working with databases in VBA the solution to that sort of problem I just switch briefly back to the VBA code is rather than attempting to convert it in every case what you'd do ordinarily is test if it's null and then you could pass in a reference in the field so for instance let me just pass in the actual word milk in this case which of course that will always return true that little expression there so if that was the case and what I could do instead is store an empty string so in VBA that would be open double quotes and close double quotes with nothing in between otherwise I would store the actual value of the field so no reference to fields I'll just put in that particular literal string just for convenience so if I were to attempt to run this simply now using f8 to step through you'll see that the S variable just retains an empty string if this wasn't no put in a literal swing there and then I would see you room the subroutine again so these I hate step through we'll see this time it has the reference to the field so that's the basic principle of how you get around that particular issue next I just like to have a very quick look at a couple of different ways of declaring string variables now the example we've used up at the top here is by far and away the most common way to declare a string variable so you state the name of the variable which in this case is nice and short and symbol s followed by the word as and the data type you want to use which in this case of course is string now the string part is actually somewhat redundant if you were using something called a type declaration character so just to demonstrate how that would work I'm going to comment out this statement that works with nulls and then I'm going to uncomment the lines that we wrote at the start but assign various different values to our string then I'm going to comment out my dim SS string just to keep it another reference and I'm going to declare the same variable again in a shorter way dim s followed by a dollar sign so the dollar sign is one example of a type declaration character and adult sign indicates a string so rather than a string just use a dollar sign after your variable name now the name of the variable isn't s dollar the name of the variable is still s so should I want to reference it later on then I still refer to it as the letter S and again if I use the f8 key to begin stepping through with the locals when they're displayed you see I've got a variable called s is values an empty string and the space type of course is a string but from that point on it works in exactly the same way as our slightly more explicitly declared variable so you can see the values get changed as I assign different values and then it disappears at the end of the procedure so obviously that's the more succinctly to declare your string variables just use you can single dollar sign rather than as string the downsides of that of course is that you need to remember what the type declaration characters are and I find that far more difficult than just the actual literal names of the data types like string or long or integer etc the other thing is it makes it slightly more awkward for your users or other people reading your code to understand so dim s dollar isn't particularly helpful less helpful I think than dim as our string so you're welcome to use them of course they still work and you do see them crop from time to time in other people's code so at least you understand what it is if you see it but for the rest of this video I'm going to carry on using the more explicit declaration as string another way to declare a string variable is to declare it as a fixed length string so all the variables we've declared so far are variable length strings you can store any amount of text up to about 2.1 for 7 billion characters and the length of the string will be equal to the number of characters stored the amount of memory taken up by your string variables is equal to is a number of bytes equal to the number of characters plus an overhead of 10 bytes to sort of help keep track of the variable size so the number of characters in plus 10 bytes a fixed length string no matter how many characters you attempt to store in the string will always have the exact number of characters you've declared so to demonstrate how that works let me comment out my dim s dollar variable and instead I'm going to say dim s as string and I'm going to set us fixed size of five characters so into a multiply symbol an asterisk and then the number five which in this case a lot allow me to do exactly five characters now the maximum value of maximum number of characters you can store in a fixed length string is only up to about sixty five and a half thousand so significantly less and the variable length strings the benefit of a fixed length string is that it the amount of memory it takes up the number of bytes it requires is equal to the number of characters so there isn't that ten byte overhead the amount of difference that makes in the real world when you're programming in VBA is so unbelievably minimal you will never notice a difference I think in using fixed length or variable length strings is quite rare to see fixed length strings used in VBA just as I was like what the slight difference is is if I was easily a beta key to begin stepping through this then you'll see that if I try to store anything you like it's about two point one four seven billion characters I don't it can also just about make out that the locals in dough sort of displays a placeholder of five characters so even though I haven't put anything into the string variable yet its length is at the moment still technically five if I use f8 it only stores the first five characters as that phrase and likewise the workbook path and then the sheet name and so is version number of the application and the sheet name and so on and so on so of course you'd only tend to use fixed length strings if you were working with a set of data whose length you knew was fixed so perhaps you're working with specific code numbers that are fixed length you can say the slightest slight bit of that or make a slight efficiency gain by using a fixed length string in those cases okay what I'd like to do next is have a quick look at one of the most common operations you're likely to perform its rings and that's concatenate them concatenated simply joining two or more strings together to form a longer string we shouldn't seem particularly complicated and you've I'm sure you've done this many times in the past but there are a few subtle little points to mention that you might not have encountered before so it's up a new module today so I'm going to right click into the project Explorer and she's in sir module and then the first subroutine in here we call something basic like basic concatenation and spell that properly basic concatenation okay what we'll do here is we'll declare three separate string variables I'm going to say dim s bomb-ass string and then I also have s 2r string I'll just declare them all on the same line for convenience and you probably guess it s3 as string as well if you prefer using the type declaration character don't let me stop you feel free to use s1 dot X 2 dot s3 dot and then I'm going to say s1 equals not very inventive got the letter A s2 equals to letter B and then s 3 is simply going to be string 1 and string to join together so to make that work all we need to do is say s1 filled by an ampersand ampersand is a standard character for concatenation in VBA and s2 all we're going to do then is we'll print them to the immediate window just to make life a little bit easier rather than stepping through and looking in the locals window I'm going to say debug print s3 so if I view the immediate window if I head to the View menu enthusing immediate window and then just use the f5 key to run the entire procedure I'll see that I end up with a and B join together in a single string rather than separately as we originally assigned them is worth mentioning that there is a different concatenation character you could use exactly the same character you'd use for adding numbers together you can replace the ampersand with a plus symbol so as long as the two values involved both strings then if I were to run that sub regime we'll end up with the same result down at the bottom now some languages don't have a separate concatenation character like VBA derse the ampersand will always concatenate the plus symbol won't always concatenate with strings you'll see an example of why it's better practice to use the ampersand rather than the plus symbol later on but it's worthwhile mentioning that you can use plus rather than ampersand to concatenate strings now of course it's not just literal strings that you can write in your code that can be concatenated you can concatenate any two strings or more strings together sort of a quick look if we make a new subroutine in fact I'm going to copy the entire subroutine we've just written and let's say some like concatenate string values so can sorry concatenate cell values as I was trying to say there so I can can Excel values will have the same three string variables s 1 2 & 3 I'll make sure that I've selected a range in sheet 1 first off I'm gonna say sheet 1 dot silic followed by range now it doesn't really matter for the sake of demonstration we can pick any cell in column a here let's go first cell a9 prisons dot select and then rather than assigning a literal string to my s1 variable I'm going to make s1 to be equal to the active cell dot value and then I'll make string two equal to the active cell dot offset 0 comma 3 valley so the value in the cell that's three columns to the right of the cell in column a will be one two three the cell which contains the run time in minutes for the particular film that I've selected so having two Maps I'm going to concatenate s1 and s2 together but I'd also like to have some kind of separate a character in between them so what I'm going to do is concatenate an additional literal string in between EC which I'll just make into a comma followed by a space so the name of the film will be separated from its runtime with a comma and a space character so having done all that if I were simply to press F turn the entire thing after he gets info so I picked Pearl Harbor sorry about that but Pearl Harbor is 183 minutes long so that's basic concatenation with cell values next I'd like to talk a little bit more about the difference between using ampersand and using the plus symbol to concatenate strings so let's have a new subroutine just down below I'm going to get myself a little bit more space so you pretty distal more clearly and create a new subroutine below using calls I'll open catenation sorry concatenation operators should be a shortened name for the procedure there okay so the basics of concatenation we already know rather than using variables this time I'm just going to jump straight into deep book printing now of course if I said debug print a ampersand B then run that one that will of course give me the letters a B in the immediate window and again if I would use a plus symbol wrap and understand then of course that would give me exactly the same result when I run it so we knew that we've established that already the same is true if I choose to concatenate two numbers if I say debug print and then say let's go for so if I say to ampersand 2 so I'm going to enter them as numeric values rather than strings if I were to debug that print of that I'm just comment out the previous one so we don't get lots of abs I'll end up with 222 and the same will be true if I were to use double quotes wraps around those numbers I'll get 22 rather than 4 you might expect so if I were using a plus symbol however to do this if I've entered both values as literal strings wrapped up in size double quotes then the result of that is the same as the concatenation using ampersand the difference of course is if these are entered as numeric values then of course you get what you expect it wouldn't be 2 out of 2 is not 22 to add 2 is 4 of course now the way thing about this is that even if one of the two values is a literal string and the other one is a numeric value then what you end up with is the result 4 so this is where the plus symbol kind of breaks down a little bit if it's definitely concatenation you're interested in if you definitely want to join one bit of text to another bit of text then it's absolutely vital to use the ampersand symbol that someone's always going to reliably join two strings together rather than try to arithmetic aliy add things so that's what we're going to go with for the rest of the video using the plus symbol can cause problems when concatenation the values of cells as well so I've just cleared out the content at the immediate window by clicking into it pressing ctrl + a and then delete to remove everything and then if I just comment out the debug print - and - what I'm going to do now is debug print the same phrase that we were generating in one of the previous routines let's in fact just go ahead backup and copy and paste a couple of bits of text from here so we take the active cell value rather than type it all out for them ampersand and then open double quotes comma space closeable quotes another ampersand and an active cell offset 0 comma 3 value of course you probably should type that up just as quickly as copying and pasting it but using ampersands always guarantees that we're always going to try to concatenate the values if I were to run that one I'll end up with more information about pearl harbor sadly but anyway there we go if I were to replace these with plus symbols instead now if I replace the first understand with a plus symbol nothing goes wrong everything is absolutely perfectly happy so it's going to just concatenate the first bit of text of a next bit of text these two values are both strings of text so the evening I'm using a plus symbol because they're both strings they're both concatenate it rather than are automatically added however if I replace the second one with a plus symbol and then try to run this on again I'm going to end up with a type mismatch error so now what what Excel is trying to do is arithmetic alee add the number of the cell 1 and H 3 to the phrase pearl harbor' comma space in that of course can't possibly ever work so there's another potential issue with using plus rather than Ambersons now one way to get around this would be to use the C STR function the C string function to convert every value into a string before it's concatenated so I could if I really wanted to copy and paste that line and then rather than trying to concatenate a number stored in that cell I could concatenate the string version of that number from that cell I comment out the previous line because we know this one isn't going to work and then run that subroutine we'll end up with the result we had expect which is all okay until I try to select another cell which contains a number so let's say just before that line I'm gonna say range a 128 dots electron picking this one for a very particular reason dot select and then if I were to step through this routine up to this point you'll see that cell a 128 contains the film 303 movie here we haven't seen it but that value is stored as a number rather than there's text so again if I try to execute this line again you can plus symbols I get another type mismatch because it's now trying to add a number to a string which of course isn't going to work so again I could make sure that I explicitly convert the cells value into a string first and then run that subroutine again and this time it will work I get the film 300 with the run time 117 minutes but why on earth you would want to go to these extraordinary lengths when you could have just replaced the plus symbols with ampersands in the first place I'm not entirely sure so you don't need to convert strings when you're using or convert are these two strings and you're using ampersands that just works automatically regardless of what the original value was it will concatenate rather than add now as well as being able to concatenate some string literals and properties of objects VBA also has some string constants for special characters things like tabs and newline characters that sort of thing so just as a very quick demonstration let's clear the contents of the image window first and we'll have a new subroutine just below let's call this one something like concatenate tabs and that gives you a clue about what we're about to do so similar idea in fact if I just copy and paste from the previous routine the debug print a and B I'm going to switch this to using the ampersand the game plus I've said we're going to focus on the ampersand rather than the plus symbol so now if I want to do if I execute it out of course I just get the letters A and B if I wanted a tab character to be inserted in between those two I can concatenate the value of the constant so in this case a constant name is VB tab which is fairly convenient and quick and easy to remember so if I were to execute this subroutine now and then it was letters a and B with a tab stop in between them now that might not appear to be all that useful immediately but imagine you were trying to generate some data to be stored in their text file and you want to generate some tab delimited data you can VB tab characters is exactly how you would go about doing that now I'm not going to go to the whole point of creating a text file and showing you all the things associated with that again we have a video from earlier in a series that explains all about generating text files whether it's comma separated values or tab delimited files but just to give you the basic principles imagine I wanted to create a single row of data based on one row from this film list and each value from each column I wanted to be separated with a tab character on the simplistic way I could do that by looping over all the cells in a row and joining them all together with a bit of concatenation so the simplest way to do that let's just get rid of the debug print zoom in there and create a couple of variables first of all so when I'd create a variable that would be a string variable and I'll use that to hold the entire string of all the film information and then I'd have another one that I'm going to pull our and this was going to be a range and assuming that I've already selected a cell in column a what I'm going to do is loop over all of the cells and as they for each are in and then I'm going to refer to arrange this from the active cell all the way to the end of the list in right with directions if I pressed ctrl and the right arrow key that would jump all the way across to the right hand side of that block of data so that assumes there are no gaps in the list of course so what I can do here is how you for each part in range active cell comma active cell end XLT right button I'll get that in the correct direction and then I'll enter next ah make sure I move on to the next cell in that row and for each row I would like to essentially build up a string in the single string variable so I want to make the string variables equal to whatever it currently contains so I'm going to make it equal to itself along with the value of the current South I'm looking at so it's going to be our value and then another ampersand and then I finally want to insert a tab character after the value of that cell so that's where I can say VB tab what I can then do right at the end after the loop is finished is I could add to say debug print S or I could say message box s want to display it temporarily on the message box and of course in the real world you'd be writing this information out into a text file as that video as I pointed out earlier on describe part 23 text files so if I was just to execute this super soon as you movie that I've got a cell in colony selected already if I run this one now I'll see my message box pops up and I've got a tab character between each individual value and if I look into the immediate window result I can see hopefully exactly the same thing there we can achieve a similar result but inserting newline characters instead of tabs so let's just copy this entire subroutine and then paste it in immediately down below and if I were to change the name of it so it says something like concatenate new lines and then of course we want to change the character that we concatenate again rather than a VB tab so I'm going to use in this case the one that you're most likely to see in the real world it's VP new line now that's not the only constant you can use to generate a new line but it's the most common one I think is the one you should use I'll explain why in just a moment but once again if I were to just execute that entire subroutine what we also end up with is a message box this time with a new line for each individual value and again in the immediate window we've got each value on a separate row in the immediate window as well one thing that's worthwhile quickly noting about both of these two previous routines is that we get an extra special character right at the very end of the string that we're building so that we get an extra tab here we get an action newline character here that's not immediately apparent when you display is on a message box I'll print it to the immediate window but if you were writing these values out to for instance text files or even cells in Excel then you might see a slightly different effect so just to demonstrate that let's have a quick modification to this concatenate new lines routine where after we finished looping what we'll do is generate a new worksheet so they worksheets stop add and then below that we'll say active cells of value equals the value of our s string variable so the active cell will be range a1 on the new worksheet that's just been created as I've made the active sheet automatically so just comment out the debug print in the message box lines and it might be easy to see from here if I run the subroutine we ought to see on a new worksheet that's generated even though the data in that cell is sitting is organized or formatted to sit at the bottom of the cell you can see there's a blank line character sitting below so if I wanted to remove that I wanted to avoid that from appearing in the first place what I could do is write a quick symbol if function into my string concatenation so switching back to the VBA editor rather than always adding a newline character I'd only want to add a new line character as long as the value of the cell one column to the right isn't an empty cell so I can do that in a couple of different ways but I think if function is a simple convenient way to do it so I'm going to add to my concatenation iif open some round brackets and we can say if our dot offset try this again offset to 0 comma 1 dot value equals excuse me I'll get this spelled correctly one column across not 10 columns cross and if that is equal to an empty string then what I would like to do is concatenate an empty string to the end of the add the line otherwise I want to add in the newline character so in there is another value to add in to the string I'll put a new line in otherwise I'll put an empty string in what I can do now if I were to run the subroutine is you can hopefully see if I were to zoom in and just change the width of that a little bit I'm now sitting that text to the bottom cell and there's no blur glein character at the bottom and that we can see the difference if I switch between the two different worksheets so one thing to watch out for in case you are using this technique to build up a string using either tabs or newline characters now as it turns out VP new line isn't the only way you can insert a new line character into a string which do a little bit of tidying up first of all in the immediate wind if I select all the text in there and delete it so I'm just going to tidy up these extra two workpiece by selecting them and then right-clicking and deleting them and I confirm that I want to do that what I'm going to do instead is create another brand new blank worksheet I'll just do this manually for the time being and what I'd like to do is create a few different bits of concatenation few different concatenation sorry that join together a simple bit of text that is a and B but using different newline characters in between so I'm going to go back to the VP editor at that point and scrolling down a little bit so let's add a new subroutine and we'll call this on something along the lines of adding new lines so we'll start with the first way well quite a couple of variables first of all let's say dim s1 as string comma s to our string and then finally s3 as drink using our inventive naming system and then I'm going to set F War s1 to B equals C letter A and s2 to be equal to the letter B doesn't really matter what values you use for this example and then s3 of course is going to be equal to s1 and then an ampersand and I'm going to go with a VB newline character first and then another ampersand and then finally s2 so I'll join all those three things together into a single string having done that I want to display that piece of information in three different ways first of all I'd like to debug print it so that I'll appear in the immediate window so I'll say debug that print s3 and I'd also like to set its pace its value in two or change the value of a cell to be equal to the value of s3 so I'm going to say in this case I'm going to say range a1 dot Val V equals s3 so for value rather than validation and that's equal to S 3 and then finally we'll show it on a message box as well now once I've set all that up it's going to run this subroutine to begin with using the VP newline character and you'll see hopefully in three different places I've got a new line for a and B in the cell a new line for a and B on the messy box and also a new line for a and B in the immediate window now there is an alternative constant you can use to join a new line in so rather than saying VP new line if I just select that text remove it and then press ctrl + space again I can look for something called of VB crlf I'm not going to attempt to pronounce that sounds a visual basic carriage return line feed which is an alternative character that you can use let's change the air the raisins referring to designs are a two and then if I run the subroutine again using get five I'll see you once again the cell contains am beyond different lines am beyond different lines in the message box and a and B on different lines in the immediate window now there's two shorter forms of this as well there's there's vbc are a most dblf so I'll just try VB LF too many websites AV blf which is just visual basic line feed and it should change the cell reference so it's a three lemon that subroutine again now once again two lines in the cells two lines in the message box two lines in the immediate window let's change it again we'll go for the vb c r but your basic carriage return now this one's going to get generate slightly different results if I switch to sell to it to refer to arrange a for this time but if I run the subroutine again I'll get you hopefully see in the background already arranged that the value of the range to the cell in the Excel worksheet gets a and B on a single line but on the message box I get a and B on separate lines and in the immediate window I get a and B on separate lines well now if you're starting to get worried budget how many different ways there are to generate a new line and wondering about which one to pick don't worry the simple rule there's always pick VB new line in VBA there's no reason whatsoever these days for the other versions VB see our lesson see our ant palace by themselves BP new line is really all you'll ever need in VBA if you're interested in why there are so many different versions of new line characters it all kind of stems from historical machine called a Tele printer there's a nice Wikipedia article here basically the only thing you need to know to understand it is that le printer had a physical printhead a carriage which Scrolls along the page to print characters and generate a new line the carriage had to return to the left so carriage return then the paper had to feed by one - the line feed so to generate a new line in a Tele printer there were two control characters pass to the system a carriage return and the line feed usually in that order now when computing systems came in to replace teleprinters they realize didn't really need a separate carriage return and line feed character to generate a new line on a visual monitor so the the control characters are broken down into carriage turns and like these CRS and elapsed there's a separate nice article here about control characters again on Wikipedia so some systems opted to choose LF the line feed and that was the most unique systems as you can see here the line P character and then carriage returns were adopted by classic Mac OS and then also the British asserted dose so CPM 80 and so dose and then hence Windows by extension so the reason why there are different types of different systems used different characters to denote a new line so these days it's really matter just in VBA just use VB new line you really really really don't need to know the other ones if you did want to show off however you wanted to look at the earth the the actual baski codes for the carriage return line feeds and that's number 13 for a carriage turn and 10 for a line feed if you really want it to impress somebody I say impress I'm not sure impress is the right word we could actually concatenate those actual characters rather than a than a constant you could use the CHR function which generates a character by passing in the ASCII code so CHR 13 would be the character term and another ampersand and CH are ten and then the next string so that would effectively choose generate the exact same results let's just set that to be range a five frequency results we're nuts that we've seen one more time I get a and B on separate lines amb on separate lines and a and B on separate lines but only only only ever use that if you're trying to impress somebody or or maybe confuse somebody as well okay so for the next part of the video I'd like to move on to look at how you compare strings so what I'm going to do is generate a new module for that and if I right click and using their module and we'll have a new sub regime called sub string comparisons this is just for the basic introduction to comparing strings I'm just going to switch back to sheet 1 because we're going to use a lot of text in column a for our string comparisons but the very first thing I'd quickly like to do is just establish how VBA treats strings of text in terms of case sensitivity so I said a quick couple of so a couple of variables like Department so I'm gonna say dim as important as strings again and just s - this time our string we won't bother with 1/3 I'm going to set s form to be equal to the letter A and then sorry that they're meant to be yes sorry yes and then su equal to the letter A as well so rather than a and B this time I'll make them the same thing and then what I'd like to do is say if s1 equals f2 then debug print the same else D book da print different I'll get there eventually there we go so the key to this is understanding how VBA treats characters of different cases currently we've got both characters in the same case so both in lowercase so I would just clear the immediate window to give us a bit more space and then run that sub regime would end up with 8 and a being exactly the same if however one of these changed to a capital letter and I try to run that one again then with the valley result being different so by default VBA treats characters with different cases as different to each other now there are a couple of different ways in which you can make string comparisons case insensitive in VBA the first technique will look at is making string comparisons case insensitive for the entire module so for any subroutine whatsoever in this entire module to do that we can add an option statement to the top of the module just like option explicit if we add option compare text that makes all string comparisons in this module case insensitive so if I were to run this subroutine again in fact let me just clear the immediate window for run the subroutine again even though these strings are in different cases when I run it now VBA treats them as the same and of course they'll still be the same even if they're in lower case of course they're always the same now does matter which combination of cases we use as long as the strings are spelt the same we'll always get the same now the default option here is option compare binary so rather than option compare text option about binaries that is false we're strings are case in Center sorry big bond case sensitive so again if I've got this one done now and I change our run submission again I get different and of course these will be disabled because of the same case and then B's will be different again because they're in different cases now one of the biggest dangers of using the option compare statement is if you start copying and pasting code between different modules those modules might have different settings for their option compare statement so what I prefer to do myself in the real world is ignore the option compare statement entirely and assume that by default every string comparison is case sensitive you can then treat each comparison on a case-by-case basis and if you need to make it case insensitive there's a couple of different approaches you can use to do that so what I'll do here is I'm going to copy and paste this line code and I'd like to make this comparison case insensitive so I can achieve that by converting both strings into the same case there's a couple of functions that allow me to do that in VBA I can convert everything into all uppercase text by using the ukase function so I say you case s1 and compare that with the you case of s2 so I might not know what case the text is in in both those if I convert everything in the string to upper case everything in a string to upper case just clear the immediate windows to demonstrate the difference here so these two strings are in different cases at the moment I'm in binary compare modes which music means it's case sensitive so the first statement returns different the second one returns the same the other function you can use here is to convert text to lowercase so in this case for not really intended but anyway M if L case s on equals L case s2 then once again we'll see the exact same things about run the switching again the first one is different because it's case sensitive the second one is the same because it's case insensitive now all the comparisons we've done so far essentially asking if one string is exactly the same as another string but what if you want to do something a bit more sophisticated than that so even something quite simple let's say I want to find all the films that begin with the letter K for example there's a couple of different ways you can approach that but what I'd like to do now is start using this list of films to return some slightly more interesting results start by clicking another new subroutine so let's call this something like wild card comparisons and if you're familiar with the term wild cards and you'll probably know what's coming at this point so each time I make a comparison here I'd like to loop through the entire list of film names to do that I'm going to use a range variable dim are as range and I'm going to say for each are in I'm going to assume that I'm on sheet 1 whenever I run this subroutine so I'm not going to qualify this with the with the worksheet name I'm going to say for each are in range a 2 so that's the top-selling list which contains a film name and then the bottom cylinder list I can reference in a variety of ways I'm going to reference it by saying a one end excel down so if I were in cell a1 in a worksheet and I press ctrl down arrow that jumps down to the last cell in their list assuming that list is continuous and it does not only breaks in it any gaps that will select the last populated cell in that column so it's assuming that's the case what I can now do is say for each are in range a to comma range a1 which is the top-selling list dot end Excel down and then close two sets of parentheses what I can then do is say next are to make sure I move on to the next one and I'm going to write a simple if statement inside this for each loop we're going to check if the value of the cell that I'm looking at and matches a particular pattern so if for example I want to do something really quick and simple I could say if r dot value equals and if I look for exactly the name Kings tonk so there are several films and lists called King Kong's then I can say end if at the bottom what I would like to do is D book print the name of the film and also it's um its release date which is in the column of the cell that's one column to the right so I can say D book dot print our dot value and understand learning some double quotes maybe a comma into space another ampersand and finally our offset to zero comma one Valley okay so just to check the business working you notice I've used capital case here to make sure that it's spelt at the same way another approach I could take using the same technique we've just talked about as I could use the L case function to convert all of the film name into lowercase text and then write out the string I'm looking for in all lowercase texts as well if I were to just simply run this one we'll loop through the entire list of films and print out all the films who are called King Kong and the release dates of those films okay so that's not really any different to what was done in the very first procedure in this module let's do the more interesting comparisons and let's say as I said I wanted to find all the films whose name begins with the letter K now one way to do this is using some awkward functions what I could try to do is extract the first letter from the film name and then check if that is equal to the letter K so one way that I could do this let's just copy and paste that if statement will do this quite a few times in this little module this is a subroutine in fact I'm gonna copy and paste the if statement comment out the first one I just like to keep a record of all the things we've done so far and rather than comparing the entire value of the cell what I want to do is compare just the first letter of the value of that cell so one way to extract the first letter is to use a function called left so we say if the L case of the left of the value of the cell and I want to return just one single character from the left so after our top value I can enter the number one enclose an extra set of parentheses and then if that is equal to the letter K in another case let's get rid of everything else except for the letter K so this bit of the function here or this bit of the expression I should say returns the first letter from the left of the cell value this function of course as we know converts into the lowercase letters and this will compare it against the lowercase letter K I'm just clear the contents of the immediate window and then run that subroutine again and you'll find that we get a list of all the films that begin with the letter K so some good and some lots of good films in that list there now of course this technique works or there's a little bit unwieldy and it's much trickier to be more sophisticated pattern match as well so let's look at what we're attempting to achieve with this SEM dissipating using wildcard comparisons I'm going to if that's going to write this new criterion from scratch let me just comment out that if statement and we'll try to return the exact same list of films but we'll do it in a much more elegant way I would like to say if L case R value and then I'm gonna need an operator called like so rather than asking if it is exactly equal to something the like operator lets me make patterned matches sort of let's say is when the value of the film name is like a string which begins with a lowercase letter K remember we've converted it to lowercase followed by any sequence of characters whatsoever if I enter an asterisk a star symbol the star symbol the asterisk represents any number of any characters is referred to as a wildcard characters could represent anything if that's true then what I would like to do is print out the film's name so let me just get again delete the contents of the immediate window and then click back into the subroutine and run that one one more time and we'll get exactly the same list that we achieved earlier on but a much simpler way to do it's much more elegant simple and string a line of code to generate the same results one of the nice things about this particular technique because it's much easier to create more sophisticated patterns as well so is very simple to change Bonta to for instance find all films that end with a letter K rather than begins with if I change where the asterisk is positioned in the string so it's asterisk case that's any sequence of any characters followed by the letter K at the end just clear the contents of the immediate window and then I could run that one again I'll find this time I get four films that end with the letter K rather than begin with so again an interesting mix of some good and some bad but you need all of them names and maps to end with letter K this also works of course for more than just a single character so rather than a letter K let's look for films that begin with the word King so let's take the asterisk away from the start and then say King followed by the asterisk and then again clear the contents of the major window and then run that one again and we'll end up with autumns whose name begins with the word King so that includes ones that aren't broken by space if you get Kings man so in Kingdom of Heaven if you want to find films that become just with the actual word King so King followed by a space for example then you simply say King space asterisk and then if I were to clear the contents of the immediate window and scroll back to the left that was an accidental click there and then run that one again I'll end up with all thumbs whose first word is the word king you can expand on this again and now search for films which contain the word king rather than begins with or ends with the word king could appear anywhere in the film's title so to achieve that all we need to do this time is put an asterisk at the start of the words King and then another asterisk at the end of the word King and I'll remove the space there as well as I get the word King anywhere in the film name clear the contents of the immediate window again just increase the height of that window a little bit so we can see some more results run that one one more time and we'll end up with even more results so you can see the word King appearing in several perhaps unexpected places and if you saw all those you'll find all sorts of different films in that list so sadly actually including two twilight movies I forgot about those I forgot I put those in there anyway I should move on to another neat thing you knew excluding particular values so if I wanted to exclude films that contain the word Twilight to my list I can do that by changing the criterion so let's have a quick go I'm going to copy this if statement this time and then make a new version of this or comment out this one just to keep it in there for reference and if I paste it in down below what I can do now is say if not L case R value like at the menses or Twilight anywhere in the list okay so if I were to clear the contents of the immediate window and then run that subroutine again it's a little less easy to see the results here because there are so many feelings are about 1200 films in the list so you can guarantee that there aren't any Twilight films left in that list now which it can only be a good thing and we've only could do that in the real world now it's a little bit less easy to see the results here so what might be nice to do is combine the films that do contain the word King but not those that contain the word Twilight so to combine criteria let's copy and paste a couple of the previous lines we've written I want to find all the films that contain the word King but don't contain the word Twilight's let's copy the previous line and then paste that in now I can comment out the line I've just copied that a number I can do is take the relevant part from the previous line so I'm searching for films where the title is like king so I can copy that part out it doesn't really matter which side I place this one I'm going to place this one just after the if keyword on this line and then I can join the two criteria together using the word and now it is important here to make sure you've included three elements to reach criterion so you must have a comparison operator which in this case is the word like and then you must have a value I decided that so the value on the left hand side is the lowercase version with the film's name and the version and the sorry the value on the right hand side is the phrase king with asterisks surrounding it then the and operator to join another criterion which in this case has a comparison operator and a value on the left hand side and a value on the right hand side what's really tempting in plain English is to do something like this where you take away the L case R value when you're writing this from scratch rather than copy pasting if I just cooked that parch to the clipboard in plain English that sounds like it makes sense if the L case of the range is value is like king and is not like Twilight then do something plain English makes perfect sense but to V be a nonsense if you try to click away from that line you'll see you get a syntax error it's highlighted in red you can't do this because this second comparison is essentially missing its first value so in between there not to like there I've got to place this bit of text back in again okay so having done that lets just clear the contents of the immediate window and then pack into the subroutine run it one more time and we'll end up with all the films that contain the word king except for happily those that contain the word Twilight and we can all sleep a little bit easier now in the last case now as it turns out the asterisk isn't the only wild card character you can use as a variety for the ones as well so where the asterisk represents any number of any characters there's another wild card that represents any one single character so let's just comment out that line and what I'd like to do this time is search for any film which contains any string of text but ends with a single character so for instance just looking at the list I've got here The Hunger Games Mockingjay part 1 and with a single character that there's a space followed by a single character so if wanted to establish or loop through or list out films that have that kind of pattern I could say if L case R value like how like properly there we go then in some double quotes I could say begins with any number of any characters so that would be an asterisk followed by a space and it ends with just one single character by itself so that would be represented by a question mark which question mark character you type in represents one single character so let's go for with a single question mark for now and then close that and then say and then and if we then were to clear the contents of the immediate window and then run the subroutine again what we ought to end up with this is a little bit difficult to see having added in the date at the end but each of these films has just a single character at the end so most as we're going to be film sequels where there with numbers but oh there we go with us a different one those American History X district 9 isn't actually and a sequel is not after district 2 3 4 5 etc and good movies worth watching actually then you can see a few more examples of that but there's always always just a single solitary character at the end of that string now as I said you can use more than just one single question mark to to represent characters let's say we wanted to find any films that have a word or have a title to end with a three-letter word so I've got a masterís representing any characters in a space and the last three characters question mark question mark question mark if I've done I think I can clear the contents of the immediate window and then run that one again what we ought to end up with this time our films whose last word is three letters long so you can see that that's absolutely the case just going back to a single question mark again and clearing the contents of the immediate window we saw that that question mark coming up isn't any character so that could be a number a bit of text or even a punctuation character if there were one at the end like a question marks for instance or an exclamation mark though there wouldn't be a space before any of those characters so you wouldn't see any those crop up in this particular example what if you were only interested in numbers so you didn't want to include things like American History X or the king and I so it could only end with a number a single digit so make that work what we could do is replace the question mark with another wild card let's just copy that line again and paste it in immediately down below and then this time rather than using the question mark character we'll use a hash symbol instead if I clear the contents of the immediate window one more time and then run this one what we'll end up with this time are only films end with a single digit so you won't see the king and I you'll see American History X etc etc now I've seen three wildcard characters used so far they asterisk the question mark and the hash symbol what if you wanted to find a string that contains one of those symbols so some of our film names for instance to end with a question mark if I wanted to find films that end with the question mark that's a little bit tricky at this point do I just copy that line and then paste it again and comment out the previous version let's say I want to find any film that contains any string of text - an asterisk but ends with a question mark character the problem with that of course is that because the question mark represents a single character at any description that basically returns every single one of the twelve in two films so it's not particularly useful while it can be instead then to treat the question mark character as an actual literal string if I can wrap it up in a set of square brackets inside string so the asterisk square brackets question mark square brackets if I remove the contents of the immediate window again getting bored of doing that by now and then run the subroutine one more time we'll see this time we've got only films whose last character is a question mark okay let's saw something else yes again let's say this time I want to find films that begin with any of these three letters a M or G just up to my initials so that's why I'm using those feel free to try a different set of initials to get a different set of results but currently knowing but I know there's not much choice of a link by combining multiple different criteria so I could do if I were to just copy this one again and then paste it in down below what I can say is if L case R value like star a and then I could use an or operator so until we use and earlier on to combine two criteria that must be met in order to returns and results we can use an or operator to combine criteria where either of them must return true so if there is so at the film's first letter where condiments say a asterisk rather than asterisk a as well by the way so if the first letter is like a star or place of in its like M star and then paste it in with it after an all against I type in all pasted them again or G star so this begins either a or M or G if I just clear the contents at the immediate window and then run that one again then I get any films that become any of those three letters that's a pretty tedious way of doing things particularly I've got more than two or three that's even worse so let's comment that one out and show you the much easier way to achieve that using some pattern matching again so I can simply say if L case sees me actually just copied in basically this but never mind if L case our value like and then if I want to refer to a number of different characters in a sequence I can wrap them up in a set of square brackets so square brackets am G close square brackets followed by an asterisk double quotes then so this will do exactly the same as the previous example it will find any any film name that begins with a or M or G followed by any sequence of any characters just to prove it let's clear the contents of the immediate window again run that subroutine one more time and you're getting exactly saying mr. results now if the letters you were testing for were in a continuous sequence so not like here a M and G don't sit next to each other in the alphabet of course but if it was say I don't know JK L and M those are the first letters you wanted to see well that's even easier to achieve there's even less typing to do things like that so I copy and paste that line and then comment out the previous one yes again and then this time I'm going to say plus a jklm I can say J - M so J 2 m followed by an asterisks that will find anything that means that is JK L or M 4 by any number of characters let's just clear the contents of the immediate window again and one more time give that one a quick test and hopefully you can see clearly you've got spare JK's and elves and M's in that list there whenever you've used these square brackets to contain a character sequence you can always reverse the logic and you can do that in a couple of different ways as well so just copy that line so currently looking but all the films at the gym excuse me let's just try it again copy that line rather than replace it with the letter C and then comment that one out and paste it in just down below so currently looking at all films that begin with either the letters J K L or M if I wanted to see all the films that don't begin with any of those like this I could say either if not L case r dot value like j 2 m and then clear the contents of the immediate window again and then run that one one more time so I get along the list and usual but if you wanted to pick through it you should find that you don't have any of those letters alternatively what I could have done again if I just copy I think that's I just comment out the line rather than copying it again and then paste it in down below an alternative way to achieve the same thing is to begin the character sequence inside the square brackets with an exclamation mark symbols of exclamation marks and more represents not effectively so if you wanted to run that one again just click contents of the immediate window and do that one more time will see exactly the same list of films again not quite so easy to see agreed but you wanted to scroll through this one more time your mortal noggin just to check that you don't get any of those characters again now although we've looked at most of these techniques individually there's nothing to stop you from combining pretty much all the techniques you've just looked at here in a single comparison so I'll just comment in fact if I just copy this on first beg your pardon and then comment that line out and then paste that one in down below and what I'm going to do is replace my entire like string so what going to this time I would like to search for any film that begins with a single character of any description followed by a space then I would like to make sure that the next characters are the first letter of the second word is definitely not an H so in a set of square brackets I can say exclamation mark H close square brackets then I would like to follow that with any sequence of characters and then at some point I want to make sure I can find an apostrophe s space so I'm going to type in a single quote followed by space Orihime s for thy space and then finally the last four characters or the last word of the of the film title must contain exactly four characters because one two three four question marks so appreciate it's not combining absolutely all the techniques but a big or a large number of them all in one single comparison so close or clear the contents of the immediate window one last time and then run the subroutine I ought to end up with two films both begin with a single character followed by space but by any number of characters with an apostrophe s of space and then a final word with a full letter so it's final word of the title with four letters in it okay so probably a little bit more elaborate than you're ever going to use in the real world but just to show just to show you what's possible with these wild cards and comparison matches okay well that's basically it for this introductory video I'm working with strings in VBA hopefully you found some of that useful and you learned at least one or two new things and I take to think you've ignited the whole video thinking well I know all this already anyway what we'll do for the next video or maybe even the next two or three videos in the series if you move on to have a look at some of the more interesting useful text functions in a bit more detail we've looked at a couple of them briefly in this video we looked at L case and u case and we also saw the left function little earlier on as well there are many many more string functions available in VBA if I have a quick preview by looking in the View menu and looking into the object browser there's a VBA library which you can select from the top drop-down list in the object browser and then a bit further down in that this as a class called strings and in there is a whole quick big long list of different string functions so what we're going to be doing later on in this series is investigating some of the more useful ones of those and explaining what they're for and how you might apply them to real world examples so hopefully you're joining us for some of those videos thanks for watching this one see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials even some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 20,244

Rating: 4.8983049 out of 5

Keywords: vba, visual basic for applications, string, strings, text, variable, cstr, convert, null, fixed length, concatenate, concatenation, ampersand, plus, tab, vbtab, new line, vbnewline, carriage return, vbcr, line feed, vblf, vbcrlf, chr(10), chr(13), case sensiive, option compare, binary, option compare text, wild card, wildcard, asterisk, question mark, pattern match, wise owl

Id: 6er1HGHFf5M

Length: 66min 43sec (4003 seconds)

Published: Sun Feb 19 2017