In this screen cast, I'm going to be going through an example of how we can implement these string functions in VBA. So, the example here I've got the Morse code, many of you are probably familiar with this. We've got letters here and then we've got dashes and dots. We're going to put in a word here, such as, hello. And then when we run this sub routine, it's going to convert that to the Morse code. This corresponds to H-E-L-L-O. Furthermore, It's going to allow the user to select where they want to put that Morse code, so what file. So, I'm just going to say coded message. Now, if I opened up the coded message, we see that that text file has the Morse code there that we export it on the spreadsheet. So, let's go ahead and work through this. Just as a note, I do have this file on the website, so you can go ahead and open this up, there's a starter file and you can use this. And by the way, I had to format cells A1 to B37 to text. So go up here in the home tab up to text, otherwise it doesn't work. So basic overview of how this is going to work, is we're going to obtain the words that's placed here that cell E7, then we're going to go character by character. We're going to first have to make this upper case because all of my letters over here are upper case. So I'm going to have to make Hello into an uppercase, then we go letter by letter using the mid function. We're going to iterate through this word and we're going to search through Column A until we have, for example a match here of H, then we're going to take the adjacent cell and that's going to be sort of the space for that first character. And then, we're going to iterate through this until we get to the length of this original word and then we're done. So, let's go ahead and get started. I've dimmed a couple of things here, L as integer, that's going to be the length of our word, wrd as a string we can get that. The word is uppercase of whatever's placed into cell E7. Again, we have to make that uppercase because all of our letters in column A are capital. So, we have to make that upper case. Another thing that I've dimmed is this letters, so letters is going to be a vector of all the letters in our string. So in our word, E7 has five letters. So, letters is going to be a vector of size five. In this case, we can obtain the length of the word using the Len function, assign that to the variable L. Then, `we ReDim that letters vector as a string. I'm also just going to use Option Base 1, I don't think it's necessary here, but I'm going to use Option Base 1 because I'm working with vectors. Now, what we're going to do is we're going to take in the word here and we're going to parse through each letter at a time, taking those letters and assigning them to this letters vector. To do this, I've implemented a for loop, where we're iterating for i as 1 to L. So letters of one will be H. We're using the mid function to extract one letter at a time from our word. So let's go ahead and work through this. I've got my Hello placed over here on the spreadsheet, I can press f8. We take in the word. You notice down here in the Locals window, that we've obtained word as Hello and it's uppercase. And then, we can go through our for loop and we're assigning then into the letters vector, the letters of our word. So, now that we've taken in the letters of the word and assigned that to the letters vector, we need to then go through on our spreadsheet over here, we need to go through Column A and search for the individual components. So up here, I'm going to dim a coded vector as a string. I'm also going to here ReDim coded, now that we know the size as a string. So it's going to be the same size as the letters vector. When we assign our letters, I'm also going to, at the same time we're going to search through using a second, so embedded for loop over the index j, which I have to dim as an integer. There are 36 different elements here in column A, so we're going to search through A1 through A36 until we find the appropriate match for that particular element of letters. So we are going to search through column A until we find an H and then, the first element of our coded vector is going to be the four asterisks here. So I have an if statement in here. If letters of i equals range A1 to A36 dot cells J comma 1. So, I'm looking through the first letter, that will be H, If that equals, then we're gonna go through the J, J from 1 to 36 as soon as we find a match, then we're going to say the IF element of the coded vector, then coded equals the corresponding element in the B column and when we find the match we're going to use that J as the output in column B. And then as soon as we find a match, we're just going to exit the for. There's no use in wasting time because we've already found a match. So, let's see if it's working up to this point. I'm just going to put a break point here and let's just go ahead and run this. We've taken in our word and now lets go through this. We have our first element of letters which is H. And now we're going to search through Column A until we find H. So, I'm just going to go through this and that should be found here. When J equals 8, H is the eighth letter and when we find a match then, the first element of the coded vector is going to be the corresponding element in the B column over here, which is four asterisks. So, I can press f8 again and I can open up down here in the Locals window, the coded vector, and we see that the first element of coded is four asterisks. And then, we exit the for because we found a match. We go to the next I which is the next letter, the next letter is E. We go through our column A until we find a match. The second element of coded is just the single asterisk and we keep going. So, that's how we can then build this coded message as a vector. The last thing we need to do, is to output the coded message onto the spreadsheet. So I'm going to just use E7 over here as the base and then we're going to use the active cell offset to just sort of start on outputting here, on the right. And I'm going to just include this in the same for loop here. So I've added this single line range E7 dot offset, I minus 1, comma 1, equals coded of I. I am also going to create a reset sub, just columns F:F dot clear. I've assigned that reset sub to this button. So, let's go ahead and see if this output is working. Let's go through this and see over here on the spreadsheet, the output, the coded H here. So, that's going to be how we do that. And we go to the next one. We find our match. Let me place the coded E here in the next cell. Finally, we want to have this message exported to a text file. So if you watch your previous screencast on how to export data from excel to a text file, you'll understand the following code. So here, I'm allowing the user to choose using this get save as file name, choose a path for the text file where they want to save that, on each line we're taking the IF element of that coded vector and writing it to a separate line of the text file. So, let's go ahead and make sure all of this works. I'm going to reset this. I've also assigned this button to this Morse coded file. We also need to make sure that we are dimming the save file as a strings. Now I think we're right to go. So I've got my hello in cell E7 and I go ahead and click okay. It runs through, we export that onto the spreadsheet, the coded message and then it asks for where we want to place that, what text file, so I'm just going to use a coded message that have already created or you can create your own and we save it as a text file. And then we can go ahead and open that up. And, this is the coded message. So this is how we can sort of implement string functions. We can write to a text file and so on. So, then in the next screen cast we're going to import the text file and we're going to convert it back to the English word. Thanks for watching.
