Everyone likes an easy solution. This month, three readers get simple but helpful advice:
- Gary learns about VBA programmatic flow.
- Sushil learns Outlook’s Bcc behaviors.
- Jessica finds an easy solution for entering formatted text.
I’m using Office 2016 on a Windows 10 64-bit system but all three solutions will work in previous versions. There’s no demonstration file.
Running a sub procedure from an IF() function
Gary wants to run a sub procedure (macro) from an IF() function when the conditional expression returns true using a syntax similar to
You can’t run a sub procedure from a function in this way, but you can use the IF() function to test for a condition and then run the macro based on the results. In Gary’s case, he might use a procedure similar to the following:
Private Sub TestValue1()
Â Â 'Call RunMacro1.
Â Â Dim booCheck As Boolean
Â Â booCheck = Sheet1.Range("B1").Value = Sheet1.Range("B2").Value
Â Â Debug.Print booCheck
Â Â If booCheck Then
Â Â Â Â Call RunMacro1
Â Â Else
Â Â End If
After declaring a Boolean variable, the code compares the contents of the two cells (B1 and B2) and defines booCheck as True or False. The IF block then runs RunMacro1 when booCheck is True. You can eliminate a few of those lines by adding the condition to the IF block as follows:
Private Sub TestValue1()
If Sheet1.Range("B1").Value = Sheet1.Range("B2").Value Then
So you have two options: Define a variable that evaluates to the test condition or call the macro from inside an IF block. The first, while a few lines longer, is helpful if you need to reuse booCheck.
Outlook Bcc exposure
Sushil and I discussed Outlook’s Bcc properties. Bcc stands for blind carbon copy–a throwback to the typewriter era. A lot has been written about Bcc’s behavior, but here’s what most people need to know:
- The Bcc recipient can see everyone who receives the message.
- To and Cc recipients can see one another but not the Bcc recipients.
- The sender and all To and Cc recipients see responses sent using Reply All.
- If a Bcc recipient uses Reply All, everyone sees it, not just the original sender.
This behavior can pose problems when someone fails to notice that they’re a Bcc recipient. The sender might want you to remain anonymous, but once you click Reply All, your participation is known to everyone else. Another problem is the use of web clients–if recipients aren’t using Outlook, you can’t control how their client handles Bcc.
There’s nothing inherently wrong with using Bcc. Understanding how it works (within Outlook) so as not to upset people is the key to using it wisely. To learn more about this feature, read Don’t turn a blind eye to Outlook Bcc etiquette.
Using AutoCorrect to enter repetitive text
Jessica wants to add a formatted AutoCorrect item. In addition, the document already contains several instances of the text and she wants to update the format for those as well. You can’t do both at the same time, but fortunately, both are easily implemented with this two-step process:
- Add a formatted AutoCorrect item to take care of new entries.
- Use Replace to format existing items.
First, let’s add a formatted AutoCorrect feature. If the text and formatting exists in the document, select it. If it doesn’t, add the text and apply the formatting directly. In our example document, shown in Figure A, the text exists but isn’t italicized, so do the following:
- Select any instance of the text Princess Warrior (the text we want italicized) and directly apply Italics.
- With the formatted text still selected, click the File tab, select Options in the left pane, and then choose Proofing. If you’re using 2007, click the Office button and choose Word Options.
- Click AutoCorrect Options in the AutoCorrect options section.
- In the resulting dialog, enter the text Princess Warrior in the Replace control and select Formatted text (Figure B).
- Click Add and then click OK twice to return to your document.
Now, when you type Princess Warrior, Word will automatically apply the italics for you. We usually think of AutoCorrect as correcting a typo; in this case, we’re using it only to apply formatting. If you misspell either word, AutoCorrect won’t work.
Format existing text.
Select the Formatted text option.
Next, let’s italicize the existing instances by using Replace:
- On the Home tab, click Replace in the Editing group.
- In the Find What control, enter Princess Warrior.
- Click inside the Replace With control; don’t skip this step.
- Click More (if necessary).
- From the Format dropdown choose Font (Figure C).
- In the resulting dialog, select Italic in the Font style control (Figure D) and click OK.
- The Replace With control remains empty, but below you’ll see the Italics format (Figure E).
- Click Replace or Replace All, depending on your needs.
- Click OK to confirm the replacement task.
- Close the Find And Replace dialog to return to your document.
After clicking inside the Replace With control, choose Font.
Specify Italic as a replacement format.
Word displays the text you want to format in the Find What control and the format you want to apply below the Replace With control.
All instances of Princess Warrior are now in italics. In addition, every time she enters that text, Word will automatically italicize it for her. If you run into an instance where you don’t want to apply italics, press [Ctrl]+Z to undo the AutoCorrect.
Bear in mind that the AutoCorrect items are an application-level feature so Word will italicize the text in all documents, not just the current one.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.