Need to type similar notes 100 times every day for 1 week? Tired of editing the details in template repetitively?
Here is a short video showing how Template Helper powered by Excel VBA (Visual Basic for Applications) can save your precious time by automatically detecting and replacing keywords in your template.
I am going to illustrate step-by-step how to develop a simple application, the Template Helper, to solve this problem
In column A, we have the template, in which words in [ ] are the parameters to be manually edited. In column B, we have the desired output. For example, we would like to replace [PHARMACIST] with my name Chris Chan. Instead of manually highlighting [PHARMACIST] and type my name, we can use REPLACE function by pressing Ctrl+F
Manually typing in the keywords to be replaced is time-consuming, especially when you have a lot of keywords to be replaced. We can utilize excel VBA to automatically detect the keywords by detecting the [ ] in our template.
The idea is to loop through every row in column A and loop through every character to identify [ and ] to record the parameter in the [ ]. The programme can then prompt the user to input values for the parameters and automatically use REPLACE function to replace the parameters with our input.
1. Loop through every row to record cell value
The lastRow variable is to identify the last row that has data. We would build a for-loop to loop through every row (from row 1 to lastRow) in our template in column A and store the cell value in column A into cellVal variable. String length of cellVal can be stored into cellValLen. We then create copy the cellVal into column B, our output. That’s why we need to clear the contents in column B beforehand using ClearContents.
2. Identify keywords in template
We then create another for-loop to loop through each character in cellVal. The Mid(string, start, [ length ]) function is a useful tool in string manipulation, which we can feed in the string, indicate the starting position and extract a certain length of string from the original string. In this case, we extract a character (length of 1) in position charPos in our cellVal. We then match the char to see if it is StartChar [ or StopChar ]. If the char is a StartChar [, we would start the recording by setting Recording to True. On the contrary, if the char is a StopChar ], we would stop the recording by setting Recording to False.
3. Store parameters
We can then define the Parameter as a string variable, and a Parameters array to store parameter. PCount is the counter for manipulating the array.
Initially, we would set the parameter counter PCount to 1 and recording to False.
While Recording is True, we can continuously add the char into Parameter. Until the char is StopChar, where we would stop the Recording and add the Parameter into our Parameters Array (Parameters). For the first parameter added, our counter PCount = 1. So Parameters(1) stores our first parameter. We then add 1 to our counter PCount so that PCount = 2 and Parameters(2) would be ready to store a second parameter later.
4. Handle duplicate parameters
Some of you may have this question in your head: what if the same parameter appears twice or more in our template? That’s very true, as our date parameter [DD/MM/YY] appears twice. Under our current logic, parameter [DD/MM/YY] would be added twice into our Parameters array. If we can check whether the current Parameter is already present in our Parameters array, the problem would be solved.
Some of you may have this question in your head: what if the same parameter appears twice or more in our template? That’s very true, as our date parameter [DD/MM/YY] appears twice. Under our current logic, parameter [DD/MM/YY] would be added twice into our Parameters array. If we can check whether the current Parameter is already present in our Parameters array, the problem would be solved.
Our solution is defining a Function IsInArray by utilizing MATCH() function. The logic is a bit complicated, so please skip to the next part and just know that this function can check whether the current Parameter is already present in our Parameters array.
For those who are interested, we can feed in the stringToBeFound and arr as our array into our MATCH() function (Match type is 0, i.e. looking for Exact Match only). If the stringToBeFound is a component of our array arr, no error would be raised, IsError() would return False, IsInArray() would return True (since we add a NOT before IsError() function). Otherwise, an error would be raised, and IsError() would return True, IsInArray() would return False.
Incorporating the IsInArray() function, we can then check whether the current Parameter is already present in our Parameters array. If not, we can add the Parameter into our Parameters array and add the counter PCount by 1.
5. Prompt user input for parameters
Declare arbitrary integers to be used in For-loop. Using i and j are just my personal preferences.
We can then loop through the Parameters array and prompt the user to input value. The prompt should look like this:
We then replace the parameters in column B with the user input. We break the loop when there is no more parameters in our Parameters array.
6. Copy the output
At last, we can automate our programme to copy the output by using this line of code
Your screen should look like this:
Summary
So there you have it! An application that can automatically recognize parameters in your templates and prompt user input to replace those parameters. This application would be especially useful when you have 1) many parameters to be replaced in a lengthy template, 2) many small templates that requires manual editing. Code it once and you can use it forever, sound like a good deal to me J
Thank you very much if you have successfully read until the end. Please feel free to leave any comments down below. J Your comments are greatly valued.
Key takeaways:
- For-Loop is a very powerful tool to loop through cells, text, array, etc
- Utilize built-in functions in your coding, e.g. MATCH(), REPLACE(), is a safe and efficient way in coding
- When you get annoyed from small, routine things in your life, think how you can streamline the logistics. Coding can be a great solution.
For the full code:
