Using VBA To Create Your Own Text To Columns Function in Excel

6 января, 2016

Articles

, , , ,

When using Excel it’s useful to have a VBA alternative to some of the standard tools that sometimes don’t quite do the job. A good example is the text to columns tool which converts delimited data into rows and columns.

This article will look at some of the alternatives to using the text to columns function.

Using The Text To Columns Function In VBA

For example, perhaps you’ve imported a list of names and countries that are in the following format:

Name, Country

Andrew Wilson, Canada

Peter Johnson, New Zealand

Maria Scopeno, Italy

To convert the data into rows and columns you can either use the Excel standard tool or apply the following code:

range("a1").currentRegion.select

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True

The problem with this approach is that it’s likely you’ll need to record a macro and then tweak the resulting code to suit your needs. Not only is that approach time-consuming, ultimately it doesn’t advance your VBA knowledge as next time you convert similar data you might need to repeat the process.

A VBA Alternative For Text To Columns

While the Excel tool or the VBA function is fast and easy to use, you might need to convert the data with more flexibility and control. To achieve the same result using raw VBA code you could do something like this:

First, select the data and loop through each item in the selection.

Range("a1").CurrentRegion.Columns(1).Select

For x = 1 To Selection.Rows.Count

Now, turn each cell value into a comma delimited array and write each item to adjacent cells.

tmp = Split(Selection(x), ",")

ActiveCell.Value = tmp(0)

For y = 1 To UBound(tmp)

ActiveCell.Offset(0, 1).Value = tmp(y)

Next

Go to the next cell and repeat the process.

ActiveCell.Offset(1, 0).Activate

Next

Applying the VBA function is definitely easier, but sometimes you’ll find it quicker with VBA methods you’re already familiar with.

Another approach, if it’s likely you’ll need the code again or throughout an application is to save the code in your own function library so you can call it up when needed.

So, instead of writing out the full function, you can call it like this:

textToCols (myRange)

Summary

While many Excel and VBA functions are useful, it’s important to know how to achieve the same result using direct code. Not only can it be quicker to work with methods you use every day, it gives you more control and flexibility in your coding.

Похожие записи