Category Archives: Word

Find and Replace in Word – Using with Wildcards and VBA

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

The Microsoft Word Find and Replace feature is very powerful and a great time saver for the more skilled user. You can use Find and Replace to locate exact words, phrases and even patterns matching various scenarios.

Word Find and Replace

Let us start with exploring how to do a regular Find and Replace in Word.

Click the Find or Replace buttons in the Home ribbon Editing section

Find and Replace in Word - Replace Button
If you want to Find a word or sentence in your Word file go to the Home ribbon tab and go to the Editing section.

  • If you want to Find click Find
  • If you want to Find and Replace click Replace
  • This will open the Find and Replace window.

    You can also use the CTRL+F keyboard shortcut to Find and the CTRL+H keyboard shortcut to do a Find and Replace.

    If you click More > > you will see the full set of options below:
    Find and Replace expanded window
    The following options are available:

    • Match case – will only find words/sentences that match the letter case (e.g. A vs a)
    • Find whole words only – will only find whole words (if looking for “ate” will only match ” ate “ and not “late”)
    • Use wildcards – allows you to use wildcards (click the Special button for list of wildcard special characters that can be used
    • Sounds like – matches expressions that sound like provided text
    • Find all word forms – matches all words/sentences that match a word form (e.g. “doyle” will also match “doyl” as it sounds similar)
    • Match prefix – match text matching a prefix of a word
    • Match suffix – match text matching a suffixof a word
    • Ignore punctuation characters – will ignore punctionation
    • Ignore white-space characters – will ignore white-space (” “)

    Provide a word, sentence and/or wildcard special characters

    Provide a word/sentence you want to Find in the Find what text field and the word/sentence you want to replace it with in the Replace with text field.

    Below and explanation of key buttons used to Find or Replace text:
    Find and Replace fields explained
    Although Find and Replace is a basic and very easy to use function it is often underestimated. Especially that many users do not know that you can easily use wildcards to replace more complex text patterns.

    Using Wilcards

    Word Wildcard Special Characters
    You can also you wildcards to replace various complex patterns such as sequences of numbers or specific number of occurances, letter cases, characters use to replace any characters and much more. To use wilcards click More > > and select the Use wilcards checkbox.

    On the right you should see all available wildcard characters.

    For more information on Special Characters that can be used in Wildcard Find and Replace read this

    Let us explore some example common scenarios below:

    Match any word made of A-Z characters, any letter case

    This matches any single word that contains A-z letters.
    The < character indicate the beginning, while > the end of a word. The [A-z] brackets indicate a series of characters, using the hyphen allows you specify the whole range of A-z letters. Lastly the @ character indicates that the previous expression may repeat 0 to any number of times.

    Match an email from the .com domain

    This matches only emails with A-z letters and 0-9 numbers in their login and domain name. Again the [A-z,0-9] bracket specifies we are listing several ranges of acceptable characters, following this with the @ characters tells that any number of these characters may appear. To use the @ character explicitly we need to escape it with a backslash \. We use the similar patter for the domain name. Finally notice again I am using < and > to indicate the beginning or end of a word as emails are not separated by spaces.

    Match a phone number split with hyphens

    The above matches any 3 series of digits separated by hyphens.

    Using Wildcards to Capture and Replace text

    In some cases you will want to not only capture a pattern but replace it with part of its content. For this you need to use Expressions (). Expressions let you mark a specific group in the “Find what” text field, that you want to reuse in your “Replace with” text field. Below a simple example:

    Example: Switch places of 2 numbers

    In this example we have a pattern of numbers separated by hyphens. Let us assume we want to switch places of these two 3-digit numbers.
    Text:

    Find what:

    Replace with:

    The resulting Text:

    Example: Replace Email domain

    Imagine you want to replace an email domain from yahoo to gmail on all emails in your Word document. If you didn’t know Expressions you would use wildcards to find a match an manually replace all such cases. However below an example that will replace this automatically:

    All Expressions () are numbered by the sequence in which they are used. This allows us to reference the first part of the email by using the backslash and number \1.

    VBA Find and Replace

    You can also execute a Find and Replace sequence using a VBA Macro:

    Find a single match

    The below procedure will print out all occurances of “Find Me” phrases.

    Find all matches

    Below VBA macro will find all emails in a Word document with their mailto hyperlinks. This is a good example of fixing hyperlinks in Word documents.

    Conclusions

    Here are my main takeaways from using Find and Replace in Microsoft Word

VBA Status Bar, Progress Bar, Sounds and Emails – Alerts in VBA

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 out of 5)
Loading...

Some VBA Macros run for ages – be it minutes or even hours. If we optimized our VBA Macro to the fullest there is often not much more you can do to change that. Usually what is irritating is waiting for VBA Macro code completion. Sitting by your desk is a waste of time – why not tell Excel/Access to let you know when the code has completed? Today we will explore such tools as the VBA Status Bar, VBA Progress Bar, Sound Notifications in VBA and Sending Email Alerts from VBA.

VBA Progress Bar

vba progress barProgress Bars are ubiquitous anywhere anything takes more than a couple of seconds to complete. So why doesn’t VBA have a native Progress Bar – beats me.

To add a VBA Progress Bar to your Visual Basic for Applications macro read my post here.

Excel Status Bar

vba status barThe VBA Status Bar is a panel that appears at the bottom of your Excel (or Access) Workbook. It is basically a Text Box to which you can display any non-blocking (non-modal) Message to your users.

Showing a Message in the VBA Status Bar

To show a message in the VBA Status Bar we need to Enable it using Application.DisplayStatusBar:

Hiding the VBA Status Bar

The VBA Status Bar when displayed, will remain displayed until ordered otherwise. It is best to hide and clean it after code execution completion:

Sound Notifications in VBA

What if you are away from your Screen, getting a Coffee or simply chatting with a colleague or getting on with our tasks stressed that we might miss that moment when our VBA Macro completes its task. Well why not introduce a Sound Notification? Let Excel or Access run a Sound to inform you that your Visual Basic for Applications macro has finally completed.

Playing a Sound in VBA

Playing a Sound in VBA is easy. Just add the below code snippet to a VBA Module:

It will play the Chimes.wav sound Once.

What if you don’t hear it? Running it once might obviously be not enough…

VBA Sound Alarm

The best approach is to create a VBA Sound Alarm that will run in a loop until we Turn it Off manually. What we will need is a Non-Modal UserForm as a pop-up to ask to stop the alarm.

First the macro code:

What happens here?:

  • I am measuring the execution time with the startTime and execTime variables
  • I am using the stopPlaying global VBA Variable to Turn Off the Alarm from our Alarm UserForm changes this value to True
  • I am displaying the AlarmForm with the Turn Off Alarm button

And now our AlarmForm code:

The result:
turn off vba sound notification

VBA Send Email Notification

Lastly when we are on the run and leaving our Workstation to process our VBA Macro remotely we might appreciate an email from Excel saying: Hey there! I just completed running the Macro!

You can find the code for the SendEmailFromOutlook function in my post here.

We might want to spice things up with maybe sending also the time it took to complete the macro:

Cool right? Makes the VBA Message Box hide in shame doesn’t it? The email should look like this:

vba email notification
VBA Email Notification

VBA FileDialog – Opening, Selecting and Saving files and folders

1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 4.38 out of 5)
Loading...

VBA File DialogOften in VBA we need to ask the users to select files or directories before we execute the actual functionality of our macro. Welcome to the VBA Open file dialog post. Today we will learn how to use the Application.FileDialog, to understand the various msoFileDialogFilePicker file dialog picking options and how to properly manage these dialogs.

Here is a simple example of a VBA File Dialog:

Application.FileDialog function

Before we start let’s understand the Application.FileDialog function.

The Application.FileDialog has the following syntax:

Parameter

MsoFileDialogType
An enumeration defining the type of file dialog to open. It has the following values:

Value Description
msoFileDialogOpen Open dialog box
msoFileDialogSaveAs Save As dialog box
msoFileDialogFilePicker File picker dialog box
msoFileDialogFolderPicker Folder picker dialog box

Properties and functions

FileDialog properties

Property Description
AllowMultiSelect Allow to select more than one file or folder
ButtonName Text displayed on the action button of a file dialog box
DialogType Change the MsoFileDialogType (see above)
Filter Set a file filter to filter file types user can select
InitialFileName The initial path to be opened e.g. C:\
InitialView The initial file view. Can be one of the following:

Value
msoFileDialogViewDetails
msoFileDialogViewLargeIcons
msoFileDialogViewList
msoFileDialogViewPreview
msoFileDialogViewProperties
msoFileDialogViewSmallIcons
msoFileDialogViewThumbnail
msoFileDialogViewWebView
SelectedItems Collection of type FileDialogSelectedItems with all selected items
Title Title of the Open file dialog window

Select files – msoFileDialogFilePicker

select file filedialogThe msoFileDialogFilePicker dialog type allows you to select one or more files.

Select single files

The most common select file scenario is asking the user to select a single file. The code below does just that:

Select multiple files

Quite common is a scenario when you are asking the user to select one or more files. The code below does just that. Notice that you need to set AllowMultiSelect to True.

Select folder – msoFileDialogFilePicker

select folder application.filedialogSelecting a folder is more simple than selecting files. However only a single folder can be select within a single dialog window.

Select folder example

The dialog below will ask the user to select a folder:

The msoFileDialogFolderPicker dialog allows you to only select a SINGLE folder and obviously does not support file folders

Open file – msoFileDialogOpen

file open application.filedialogOpening files is much more simple as it usually involves a single file. The only difference between the behavior between Selecting and Opening files are button labels.

Open file example

The dialog below will ask the user to select a file to open:

Save file – msoFileDialogSaveAs

saveas application.filedialogSaving a file is similarly easy, and also only the buttons are differently named.

The save file dialog will in fact not save any files! It will just allow the user to select a filename for the file. You need to open the files for reading / writing yourself. Check out my post on how to write files in VBA

Save file example

The dialog below will ask the user to select a path to which a files is to be saved:

The msoFileDialogSaveAs dialog does NOT support file filters

FileDialog Filters

One of the common problems with working with the Application.FileDialog is setting multiple file filters. Below some common examples of how to do this properly. To add a filter for multiple files use the semicolor ;:

Be sure to clear your list of filters each time. The FileDialog has its nuisances and often filters are not cleared automatically. Hence, when creating multiple dialogs you might see filters coming from previous executed dialogs if not cleared and re-initiated properly.

The open file dialog will in fact not open any files! It will just allow the user to select files to open. You need to open the files for reading / writing yourself. Check out my posts:

Do you really need Visual Basic for Applications macro for that?

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Visual Basic for Applications (abbrv. VBA) is often misused causing issues with maintaining code, backward compatibility with older Excel version and other issues. Reason being is often that some of us don’t know, or better still, don’t make the minimal effort to learn ready available tools in Excel or other Office applications. From time to time I see some Excel macro that does the job of a Excel PivotTable or more often that of a simple MS Query (SQL). So I am dedicating today’s post to those struggling with their doubts on whether to open up the VBE (Visual Basic Editor).

More often than not, Excel users tend to create macros in situations where they could easily leverage an Excel formula, PivotTable, AddIn or other ready solution instead of writing VBA Code. So let us ask ourselves today:

Should I use a Visual Basic for Applications macro for that?

Do your research

Google your problem!
Google your problem!
What is the reason you consider resorting to VBA? Did you really do your research and you are sure there are no native Excel functions that will do the job? Too often I see people using VBA Macros because they didn’t do a 10 min Google search to see if there are Excel formulas that will do the job.

Does Excel have a built in tool for that?

Excel is the best entry level Data Analysis Tool out there. Are you sure you know all the tools natively available in Excel? Check the Data ribbon tab first!

None of them fit the issue?

Can you use an Array Formulas (CTRL+SHIFT+ENTER)?

Often what we need is to create a complex formula that will run on an entire array of cells or rows, not just a single cell.
Are you aware of Array Formulas? They allow you to do operations on whole arrays (rows/columns) of Excel data. A simple example below to demonstrate the power of Array Formulas:

The Table

A
2
5
11
12

The Array Formula (type and hit CTRL+SHIFT+ENTER):

=COUNT(IF(A1:A10 > 10;A1:A10))

The result (number of cells with value above 10):

2

As you can see the formula above can easily count all cells between A1:A10 of value higher that 1. Nice right? That is just the peak of the iceberg of what Array Formulas can do. Check out this Array Formula tutorial to learn more.

Is there an App or AddIn for that?

I know some of us like to be creative and look to code stuff their selves. However, I do encourage that you check for existing solutions:

No alternatives – need to use VBA

Ok so there doesn’t seem to be any alternative to writing a VBA macro. Where to start before you begin?

Need help / support

If you stumble across issues when developing your VBA code, feel free to post your questions (do some research first!) on one of these forums:

  • StackOverflow – in my opinion the best technical forum out there
  • AnalystCave.com forums – got a question to one of my posts or tools? Feel free to reach out!
  • MSDN Excel forum – the official Excel forum by Microsoft
  • MSDN VBA forum – the official VBA forum by Microsoft
  • MrExcel forum – no longer the power house it used to be, but still hosts a bunch of Excel users that share their knowledge and can help
Words of advice when posting questions: most forums have a policy to ignore (at best) questions which don’t show that you made any effort to resolve the problem yourself. Therefore when posting a question remember to follow the above steps or at least:

  • Google your question
  • Search the Forum database for similar questions.. you would be surprised how many people had the same problem as you did
  • Include a summary of your research in the question
  • Include your sourcecode (line that throws error even better) or be sure to explain the issue in detail

On the other hand don’t:

  • Post questions showing negligence to details and quality – lots of typos, misuse of formatting etc.
  • Write general statements that nothing works or post questions with general titles like – Excel Problem or VBA Issue
  • Demand that someone write the code for you (really, it happens)

Why am I discouraging the use of Visual Basic for Applications?

Don’t get me wrong – my blog is ALL about VBA. It is not that I discourage the use of VBA, in many cases you won’t have an alternative. It is just that in so many cases we are incorrectly lazy enough to avoid doing proper research that we actually spend more time writing unnecessary VBA scripts that copy often built in Excel capabilities. So many enterprises rely on VBA scripts that often do nothing more than a simple Pivot Table, Array Formula or PowerQuery/MS Query could do in seconds.

So seriously Help Yourself and do your research first. You will appreciate VBA when you will really need it.

Excel VBA Option Explicit – explicit variable declaration

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

The Excel VBA Option Explicit statement is such useful feature in Excel VBA, it is strange, this is the first time I am writing a post about it. It proves incredibly useful in preventing typos and other stupid mistakes when developing Visual Basic for Applications macros. But what does it actually do?

What does Option Explicit do?

The VBA Option Explicit statement can either:

  • Forces an explicit declaration of all variables in a VBA Module
  • Allow the implicit declaration of variables in VBA Modules

However, the first question most people have is…

What is explicit and implicit variable declaration?

Explicit variable declaration in VBA is the declaration of a variable with the use of the VBA Dim statement:

Dim x as Long 'Explicit declaration
x = 1

Implicit variable declaration on the other hand is the opposite – the usage of a variable without any preceding declaration. The VBA Compiler will create a variable that has been used even if it was not preceded with an explicit declaration:

x = 1 'Implicit declaration

Option Explicit usage

Now that we know what implicit and explicit declarations we can ponder on a second on why we would want to force explicit variable declaration. Well, for a number of reasons:

  • To prevent variable name typos
  • Clean up VBA Code
  • Optimize VBA performance
By default all implicit variables are Variant types. Variants are much less efficient than other data types (Longs, Integers, Strings etc.). Read more on VBA Performance here

Using Option Explicit

Option Explicit 'Declare that all variables are to be explicitly declared

Sub Main
 Dim x as Long
 x = 1 'OK!
 y = 10 'ERROR!
End Sub

option explicit errorWhat happens when you try to declare a variable implicitly. Believe me, it is better to fix Variable not defined errors than scramble to find variable name typos.

Turn on Option Explicit by default

For me the Option Explicit statement is a must requirement. I turn it on by default in all my macros. Even when using Variant variables.

turn on option explicitHow to turn on Option Explicit by default for all VBA Code Modules? Go to Tools->Options.... In the Options window select Require variable declaration. That is it! Similarly you can turn off the addition of the Option Explicit statement in all your VBA Code Modules by unchecking this option.