Category Archives: Outlook

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:

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.

Making proper VBA Comments

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

Today’s Tip of the Day will focus on VBA Comments, how to make comments in VBA and how to make the most of them. Comments are needed in any script to explain what the code is supposed to do or provide some verbatim on certain lines / blocks of code. Scripts without comments are a nightmare for many of us – making it sometimes impossible to read through the intentions of the author. If you want to make it as easy as possible for someone to reuse your code – learn how to make proper comments.

How to make comments in VBA?

VBA Comments
Opening the Edit Toolbar to comment/uncomment code
VBA Comments are lines of code preceded with the comment character '. VBA supports only single-line comments. Everything after the ' character will be ignored by the Visual Basic for Applications compiler.

Alternatively, to make comments, you can utilize the Edit toolbar from the View->Toolbars menu in VBA Project.

Below a simple example of a single line VBA comment:

Dim x As Long
'This is a comment
x = 10

A multiline VBA comment:

Dim x As Long
'This is a 
'multiple line
'comment
x = 10
Little known fact: you can also create comments in VBA by preceding them with the REM keyword, although it does not make much sense to do so:

'This is a comment
Rem this is also a comment in VBA!

VBA Comments – Best practices

Making VBA comments is not hard. It is the rule of making proper comments in the right place that make a difference. Below I am list a couple best practices that I think anyone writing Visual Basic for Applications scripts should adopt.

Commenting procedures

Make sure to place comments within VBA procedures (Functions and Subs) that provide a more complex capability.

Comment the purpose of procedures (Subs and Functions) . Ideally each procedure should accomplish a single tasks and have no more lines of code than can be made visible on your screen. A procedure should also be described by:

  • Explaining the purpose / goal of the procedure e.g. read file
  • If needed, describing the parameters and outputs

A simple example below:

Function ReadFile(fileName as String, length as Long) as String
'Read a text file, and return [length] first characters
'fileName - name of the file to open
'length - number of characters to read
  '...
  '...
End Function

Comment associated blocks of code. Usually your procedure will consist of several block of code, each one realizing a different activity. Providing even a single line of comment to explain each block of code will make your code more readable.

Explain conditional statements. Conditional statements are your codes Fork to different execution pathways. If the code does not speak for itself it makes sense to provide some verbatim.

If age < 18 Then 'Can't drink or drive
  '...
ElseIf age < 21 Then 'Can drive, can't drink
  '...
Else 'Can drink and drive. But not simultaneously :)
  '...
End If

Excel VBA Function vs VBA Sub – Excel Functions and Procedures

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

A VBA Function can accept parameters and return results. Functions, however, can’t be executed directly. On the other hand a VBA Sub procedure can be executed directly and can also accept parameters. Procedures, however, do not return values.

We often use Subs and Functions often not thinking about their true potential and how much we can squeeze out of a regular VBA Function. Let’s start with a reminder how both differ from each other and then dive into the details.

VBA Sub, VBA Function Syntax

Below structure resembles a VBA Sub and VBA Function:

Below are simple examples of Subs and Functions:

VBA Sub procedure syntax

The code block of a VBA Sub procedure is marked by the Sub and End Sub statements.
VBA Sub procedure

VBA Function procedure syntax

The code block of a VBA Function is marked by the Function and End Function statements.
VBA Function

VBA Function vs VBA Sub

We often tend to mix up procedures, Subs and Functions in VBA. So let’s get it right this time. There are 2 main differences between VBA Procedures (Subs) and VBA Functions:

  • VBA Functions return values, VBA Subs don’t
  • You can execute a VBA Sub, you can’t execute VBA Functions – they can only be executed by VBA Subs

Executing Functions and Subs

Although I provided examples above there are multiple ways to execute a VBA Function and a VBA Sub:

Passing arguments ByVal and ByRef

The common knowledge is that only VBA Functions can return values. That indeed is mostly true. However, instead of a value (the result of the Function), we can assign values to variables that can be passed to the procedure by reference. What does that mean?

Variables can either by passed to a procedure (Function, Sub etc.) by their value or their reference.
Passing by value translates to making a copy of the variable. Thus any changes to the copy will not be reflected in the original variable.
Passing by reference is passing the address of the variable to the procedure. This means that any changes to a argument will be reflected in the original variable.

ByVal vs ByRef
Passing a variable by its Value or its Reference

By default all arguments are passed to procedures ByVal, so that no changes can be made to the original variables

ByVal and ByRef examples

Let’s look at some examples now:

When passing by value the variable someLong is not modified. However, when we pass it by reference its value is changed within the Sub procedure.

Objects and arrays are always passed by reference, because objects are in fact references (e.g. a Collection). Beware in such cases not to modify referenced objects by mistake!

Passing arrays to Subs and Functions

You can also easily pass arrays to Subs or Functions, even redefining their length. See example below:

Optional parameters

VBA Functions and Subs permit optional parameters, ones that need not be provided when executing the Function or Sub. It is a good practice to specify the default value for such parameters. See example below:

You can verify if a parameter has not been passed to an Sub or Function by using the IsMissing function. The IsMissing function works however only for Variant type parameters. See the same SayHi procedure as above, this time with the IsMissing function.

Dynamic parameter list

Say you want to create a VBA function like the Excel SUM or AVERAGE formulas – that can be provided with a dynamic list of parameters. VBA extends a neat solution for such a scenario called the ParamArray.

The ParamArray transforms a list of Variant variables passed as parameters into a neat Variant array.