Category Archives: Word

VBA End – Exit or Reset VBA macro

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

Often we think about beginnings not endings. It is only when we get somewhere it is when we realize we hadn’t thought through our destination. Same goes for writing VBA macros. Fortunately the VBA End statement is here to save the day and help us put that full-stop in the right place. Today I want to share a cool VBA tip for properly ending your VBA macros.

VBA End vs VBA Exit

The VBA End statement ends the execution of a certain VBA scope.

VBA End statement

The VBA End Statement is used to mark the end of a specific VBA scope:

You can End a Function, a conditional If statement, mark the End of an VBA Enum or VBA Type.

VBA Exit statement

The VBA Exit Statement is used to exit a particular scope earlier than defined by the VBA End Statement.

See example below to understand the difference between VBA End and VBA Exit:

Exiting VBA Functions or Procedures

Sometimes however we want to end the execution of a function or procedure early, simply by leaving the current execution scope.

Exit Sub

Ending Sub procedures is very useful. Instead of using the End statement we need to use the VBA Exit statement.

Exit Function

Similarly for Functions we need to use the Exit statement instead of End.

The result of the VBA Function above is 1 not 2.

Exit VBA Loop

You can also use Exit to exit a VBA Loop statement:

The result of the VBA Function above is 1 not 2.

End VBA macro / Reset VBA macro

Now here is the trick I want to sell you today. The Exit statement above will allow you to leave the scope of your current VBA Function or VBA Sub. It will not however end the execution of your entire macro. A simple example below:

The result:

The result of running Exit Sub
The result of running End Sub

So you see Exit Sub exiting only the the current scope of the running VBA Sub procedure and not the entire macro!

End – as simple as that

How to exit the execution of the entire macro (the Main Sub)? A simple End will do…

The result:

Using the VBA End statement to exit macro execution
Using the VBA End statement to exit macro execution

The End statement ends the execution of the ENTIRE MACRO.

Use End carefully!

As Andy Pope has rightfully corrected me, the End statement needs to be used with care. As there are consequences…

The VBA Reset button
The VBA Reset button
The VBA End statement works a lot like the VBA Reset button. This is what happens:

  • Object events will not be invoked e.g. the Unload, QueryUnload, or Terminate events
  • Any running Visual Basic code will be stopped – that means Timers and other running code
  • Object references held by other programs are invalidated – if any application is holding a reference to a VBA object it will be invalidated and inaccessible
  • Closes any open dialogs / forms (e.g. UserForm or MsgBox)

Using End is a lot like pulling a hand brake in a car. It stops the car, but often so abruptly it might cause it to crash.

VBA Substring vs Excel Substring – How to get a Substring in Excel?

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

Strings are not easy to manipulate and often we need to create a Excel Substring or VBA Substring. Excel and VBA in fact have multiple functions that support obtaining substrings.

Excel vs VBA Substring functions

In the table below I compared the basic Excel and VBA Substring functions.

Description Excel Function VBA Function
Get # characters from the left
Read more here.
Get # characters from the right
Read more here.
Get # characters from the middle MID(text_string, start_number, char_numbers) Mid(text_string, start_number, char_numbers)
Read more here.
Split string with delimiter
No function available in Excel
Split ( expression [,delimiter] [,limit] [,compare] )
Read more here.

Get VBA Substring example

VBA Substring examples

The Left, Right and Mid functions are the basic Excel Substring functions. Below are some examples of Excel substrings using these:

Excel VBA Substring Examples
Excel Substring Examples

VBA Substring examples

Below similar examples using a VBA macro. Additionally in VBA you can use the VBA Split function.

Excel Substrings using FIND

In some cases the substring you want to find is dependent on the placement of a certain character or another substring within your text. To extract the data you need you will need to use either the Excel Find function or the VBA InStr function. In the example below I am extracting the Year and Month using the LEFT, RIGHT and FIND functions.

Excel VBA Substring Find examples
Excel Substring Find examples

What does Find do? If return the index position of a certain character or substring within a given string. Using this I can leverage the LEFT and RIGHT function to extract the data around the hyphen character - wherever it will be placed within a string.

VBA Substrings using InStr and InStrRev

VBA Substrings using Regex

By using Regular Expressions in VBA you can also split strings or get substrings based on virtually any defined pattern. In this post here I show how you can define a new User Defined Function which you can use as an Excel function to get a substring based on a Regular Expression pattern. See example below:

Get Excel VBA Substring using Regex in Excel
Get substring using Regex in Excel

VBA String Functions reference

When obtaining substrings it is worth learning other useful VBA String Functions such as InStr. Do see my VBA String Functions reference to learn more!

Excel VBA String Functions
VBA String Functions

VBA Like Operator – Using Wildcards in Conditional Statements

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

The VBA Like operator is something so useful I am often surprised how rarely it is used in Excel and Access VBA. I often tend to see the Like operator as the last resort before using Regular Expressions in VBA. It replaces greatly the VBA InStr function when needing to check if a certain substring is present within a certain string. So let’s get right to it!

Here is a simple example of using the Like operator:

The Like operator is an easier alternative to VBA Regex which allows you to search for almost any pattern within a string and find matches.

In case you want to not only find check if the string contains a substring but also return the position of the match – best try using the VBA InStr function.

VBA Like operator

The VBA Like operator is a boolean operator that return True if a string is matched against a certain string pattern.

VBA Like allows you also to use the following wildcards to replace certain strings or characters:

Wildcard Description
* matches any number of characters
? matches any 1 character
[ ] matches any 1 character specified between the brackets
matches any range of characters e.g. [a-z] matches any non-capital 1 letter of the alphabet
# matches any digit character
The Like operator is not letter case sensitive! In case you need upper/lower case matching use VBA InStr function instead

And that is basically it. Easy right? Let’s see some examples…

VBA Like operator examples

Let us now look at a couple of examples that greatly show all the possibilities of the Like operator:

Matching against letters

Matching against numbers

Matching a phone number with either dashes or dots

Matching a certain string within another string

As you can see in the first row we are using the Like Operator similarly as we use other compare operators (=, >, <, <>). The Like operator let’s you validate if a string on the left side of the operator matches the Like expression on the right.

You can find the official Like MSDN documentation here

Conculsions on the VBA Like operator

My main take-aways are:

Word Translator AddIn and Excel Translator AddIn

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

Working in multinational organisations many of us struggle with ad-hoc translations of Word documents or Excel spreadsheets. Translations are a boring task. Usually what most of us have mastered is a simple process of copying texts from documents to Google Translate and back. If you have my VBA Time Saver AddIn you probably already have a neat VBA function for translations which you can add as a shortcut. However, on a daily basis if you often switch between languages you might want an AddIn instead. Thankfully I am introducing the Word Translator AddIn and the Excel Translator AddIn!

Word Translator AddIn: Translate text in Word

Word Translator AddIn
Word Translator AddIn
The Word Translator AddIn is very simple to use. First set the from and to languages. Now all you need to do is select any text and hit the Translate button (or CTRL+SHIFT+T shortcut)!

The AddIn is provided with a list of supported languages. Making it easy to simply select the language you want to translate from and to in Word. Hitting the translate button with result in the text being translated and replaced, however the formatting might change – so watch out!

The Undo functionality does not work when running a VBA macro in Word. A translated text can’t be undone. Undo will however work properly in other cases of course

Install Word Translator AddIn

Download the Word Translator AddIn

Download the Word Translator AddIn from here. Provide your email to get the download link:

Copy the AddIn to your Word Startup folder

Copy the dotm template document to your Word Startup folder. In most Windows systems it should be similar to:

C:\Users\[user.name]\AppData\Roaming\Microsoft\Word\STARTUP

Remember to replace

[user.name]

with your username!

Excel Translator AddIn: Translate text in Excel

excel translator addin example
The Excel Translator AddIn appears as an additional Ribbon tab
The Excel Translator AddIn is similarly simple to use. First set the from and to languages. Now all you need to do is select any amount of Excel ranges and hit the Translate button (or CTRL+SHIFT+T shortcut)!

The Undo functionality does not work when running a VBA macro in Excel. A translated text can’t be undone. Undo will however work properly in other cases of course

Install Excel Translator AddIn

Download the Excel Translator AddIn

Download the Excel Translator AddIn from here. Provide your email to get the download link:

Copy the AddIn to your Microsoft AddIns folder

Copy the xlam AddIn Workbook to your Microsoft AddIns folder. In most Windows systems it should be similar to:

C:\Users\[user.name]\AppData\Roaming\Microsoft\AddIns

Remember to replace

[user.name]

with your username!

How Word and Excel translation works

The Word and Excel Translator AddIns are proudly powered by the Google Translate engine. If you want to learn more read my post here. Do read more on how to use Google Translate responsibly.

Summary

Translations in Word and Excel can be a dull task. With the use of the above AddIns I was however able to translate my documents in 50% less time, giving me more time to check the quality of the translations and make minor language fixes.

Issues

Don’t post issues/error as comments below! Please post them on my Questions page.

The AddIns are powered by Google Translate functionality and require a working direct Internet Connection. If you get errors, most likely you are working from behind a proxy server and need to connect directly to the Internet.

Merge CSV files or TXT files in a folder – using Excel or CMD

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

Often we face the challenge of having to merge csv files or txt files in a folder, into a single file. Excel is the obvious tool for such tasks and today I will show a couple of easy ways for merging multiple files, in a single or even a whole structure of folders, into a single CSV or text file. To merge csv files or other text files it is often best to use Visual Basic for Applications in Excel.

Let’s start with the simplest approach using Windows Command line without having to use Excel.

Merge CSV files using Windows CMD

This approach uses the Windows Command line Copy command.

Open the folder which should contain your CSV or TXT files

Open in Windows Explorer the folder containing CSV or TXT files to be merged. These should be without headers or only the first file should be with headers.
merge CSV files Windows CMD

Open CMD command line within folder

Click on the filepath of the Windows Explorer window and type cmd and hit ENTER.
merge CSV files Windows CMD

Merge the files using COPY command

The CMD Windows command line Window should open. Type the following command and hit ENTER to merge files

copy *.csv merge.csv

merge CSV files Windows CMD
The result will be the newly created merge.csv file with merged data across all CSV files within the directory.

Simply replace *.csv with *.txt to merge text files instead of CSV files

Merge list of csv, txt files

The previous method was very simple and didn’t require the use of Excel or MS Office. The below and the following approaches will provided you with more flexibility when merging files. If you don’t know how to use Macros in Excel read my Tutorial first.

Assuming you want to merge a list of files in a String Array you can use the procedure below. It will merge all provided csv or text files into a single new text file.

Sub MergeFiles(fileNames() As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    For Each fileName In fileNames
        fileNo = FreeFile
        Open fileName For Input As #fileNo
        textData = Input$(LOF(fileNo), fileNo)
        Close #fileNo
        If headers Then
            result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
            firstHeader = False
        Else
            result = result & IIf(addNewLine, vbNewLine, "") & textData
        End If
    Next fileName
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Merge csv, txt files example

See an example below of how to use the MergeFiles procedure:

Dim fileNames(0 To 1) As String
fileNames(0) = "C:\somefolder\test.csv"
fileNames(1) = "C:\somefolder\test1.csv"
    
MergeFiles fileNames, "C:\Merged.csv", True, False

MergeFiles Parameters

fileNames()
Array of Strings representing full file paths to files that are to be merged

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for CSV TXT files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge csv, txt files within specified folder

Another case is when you want to merge all csv files within a single folder. This procedure is similar to the previous one with the exception that it runs through all files within a single directory (excluding subdirectories – for that scroll to next procedure). You can also use wildcards such as “*.csv” to be sure that only csv files are merged a not other files – read my post on the VBA Dir function to learn more.

Sub MergeFilesInFolder(folderName As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    fileName = Dir(folderName)
    Do Until fileName = ""
        fileNo = FreeFile
        Open (Left(folderName, InStrRev(folderName, "\")) & fileName) For Input As #fileNo
        textData = Input$(LOF(fileNo), fileNo)
        Close #fileNo
        If headers Then
            result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
            firstHeader = False
        Else
            result = result & IIf(addNewLine, vbNewLine, "") & textData
        End If
        fileName = Dir
    Loop
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Merge csv, txt files in folder example

See an example below of how to use the MergeFilesInFolder procedure:

MergeFilesInFolder "C:\somefolder\*.csv", "C:\MergedFolder.csv", True, False

MergeFilesInFolder Parameters

folderName
A folder including all files to be merged. Wildcards are permitted if supported by the VBA Dir function

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for csv files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge csv, txt files within all subfolders

The most complex case is when you want to merge files not only within a certain directory but also within all subdirectories. This will equally work for a scenario when there are no subfolders.

Sub MergeFilesInSubFolders(folderName As String, pattern As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, folder As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    Dim col As Collection
    Set col = New Collection
    col.Add folderName
    TraversePath folderName, col
    For Each folder In col
        fileName = Dir(folder & pattern)
        Do Until fileName = ""
            fileNo = FreeFile
            Open (Left(folder, InStrRev(folder, "\")) & fileName) For Input As #fileNo
            textData = Input$(LOF(fileNo), fileNo)
            Close #fileNo
            If headers Then
                result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
                firstHeader = False
            Else
                result = result & IIf(addNewLine, vbNewLine, "") & textData
            End If
            fileName = Dir
        Loop
    Next folder
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Function TraversePath(path As Variant, allDirCollection As Collection)
    Dim currentPath As String, directory As Variant
    Dim dirCollection As Collection
    Set dirCollection = New Collection
     
    currentPath = Dir(path, vbDirectory)
    'Explore current directory
    Do Until currentPath = vbNullString
        If Left(currentPath, 1) <> "." And Left(currentPath, 2) <> ".." And _
            (GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then
            dirCollection.Add path & currentPath & "\"
            allDirCollection.Add path & currentPath & "\"
        End If
        currentPath = Dir()
    Loop
     
    'Explore subsequent directories
    For Each directory In dirCollection
        TraversePath directory, allDirCollection
    Next directory
End Function
Read this post to learn more on using the VBA Dir function to traverse directories and subdirectories

Merge csv, txt files within subfolders example

See an example below of how to use the MergeFilesInSubFoldersprocedure:

MergeFilesInSubFolders "C:\somefolder\", "C:\MergedSubFolders.csv", True, False

MergeFilesInSubFolders Parameters

folderName
A folder with or without subfolders including all files to be merged. Use wildcards with pattern parameter

pattern
If needed a pattern using wildcards permitted by the VBA Dir function

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for csv files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge CSV files – filter records

Sometimes we want to download just a subset of records in our CSV files. One way is uploading the data and then filtering it in Excel. But why not do it in one go? See my SQL AddIn or my read CSV file using SQL example in this post here.