Spreading an Array values across Columns in Excel – VBA

.

All my avid readers from Computer Programming backgrounds must be thinking is it a big deal? Like other programming languages use loop to traverse all the values of an array and spread them across rows or column. Right?
 
If you are thinking so… you are absolutely right. You can do it using the loop.
 
But what if I tell you that there is a very easy and faster way to do so? Excited??
 
Generally, as a developer, we tend to forget the performance aspect while coding… be it a code for a small task or a big software. Performance matters. None of us like the below screen while watching an exciting Cricket Match on Internet or a Movie etc. Then why ignore the performance aspect??
 

Buffering Issue in Programming

Buffering Delay

 
So let’s learn some faster technique to solve the above task. In the above task performance does not really matters when Array is smaller in size. But when Array size is bigger (like in Thousands..) then you will realize the performance issue.

 
So without wasting any more time let’s come to the point. In this article I will give you both the codes to spread the values of an array towards Rows or Columns. At the end of the article you will find a downloadable which will show you the performance difference.

1. Slower Method – Array values across columns – Using Loop

Below is the traditional code to traverse an array value using loop and using this all the values of an array will be spread across columns.
 


Sub SpreadUsingLoop()
'This function will spread the array values using For loop
    
    Dim myArr(10000) As Variant
    Dim TimeDuration As Long
    Dim StartTime As Date
    Dim EndTime As Date
    
    StartTime = Now 'Start time is captured here
    
'fill values in this array
    For i = 0 To UBound(myArr) - 1
        myArr(i) = "This is my data " & i
    Next
'myArr has 5000 values in it.
'to spread this 5000 values, here we will use loop
    Rows(6).Clear
    
    For i = 0 To UBound(myArr) - 1
        Cells(6, i + 1).Value = myArr(i)
    Next
    EndTime = Now  ' End time is captured
'Calculate the total time duration in seconds
    TimeDuration = DateDiff("s", CDate(StartTime), CDate(EndTime))
'Show the time taken in Message Box
    MsgBox TimeDuration & " Second(s)"
End Sub

2. Faster Method – Array values across columns – Using Array to Range

Below is the BEST way to spread an array values across columns.

 


Sub SpreadUsingArrayToRange()
    
'This function will spread the array values using For Array to Range
    
    Dim myArr(10000) As Variant
    Dim TimeDuration As Long
    Dim StartTime As Date
    Dim EndTime As Date
    
    StartTime = Now 'Start time is captured here
    
'fill values in this array
    For i = 0 To UBound(myArr) - 1
        myArr(i) = "This is my data " & i
    Next
'myArr has 5000 values in it.
'to spread this 10000 values, here we will use array to range method
    Rows(11).Clear
    Range(Cells(11, 1), Cells(11, UBound(myArr))).Value = myArr
    EndTime = Now  ' End time is captured
'Calculate the total time duration in Seconds
    TimeDuration = DateDiff("s", CDate(StartTime), CDate(EndTime))
'Show the time taken in Message Box
    MsgBox TimeDuration & " Second(s)"
    
End Sub

 

How is the second method better?

i) Simpler in coding – Single line code

In the second method you need not to write a looping code of 3 lines with a traversing variable “i”. Thus it makes your coding easy, short and understandable.

ii) Faster – Better Performance

This is the Major benefit of the second method. This method is way faster than the First one. For 5000 Records you can see the difference by running both the codes. My observation is that – First code always takes 2 to 3 seconds for 5000 records while the second method completes in ZERO seconds.

FREE Download - View Performance of Spread Arrays across columns

To see the performance and play around with the code, you can download the below excel workbook with both the codes with a User Interface button.

VBA Animation to see - Array Traverse Performance

VBA Animation to see – Array Traverse Performance

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

3 Comments

  1. Rudra Sharma

    Hi Vish,

    Thanks for the article. I am aware that 'Arrays' are of great importance in any programming language but I have very negligible knowledge of it.So if time and situation pertmit may I request you to write an article on this Topic(Array)?

    I know there are plenty of tutorials available in internet but I am used to your articles..

    With Regards

    Rudra

    Reply
  2. Ravi

    Hi Vishwa,

    I agree that second method is faster than first one.

    But second method is just working like copy and paste. So, it’s giving the same result as in First cell.
    In that case first method is far better than second method. because first method is giving accurate result.

    Reply
  3. Vishwamitra Mishra

    Dear Ravi,

    Thanks for your feedback. I appreciate this. Can you please explain me how are you getting in-accurate values in second method?

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro Spreading an Array values across Rows in Excel - VBA - [...] friends,   This is an extension to my previous Article. Where we learnt a best way to spread an…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest