Writing To A Range Using VBA

Posted on by John Walkenbach

If you need to use a VBA procedure to write values to a range, most people would probably create a loop and write the values one cell at a time. Like this:

>On my system, writing 100,000 values using a loop takes 9.73 seconds.

A faster way to accomplish the task is to put the value in an array, and then transfer the array to the worksheet. The procedure below writes 100,000 values in 0.16 second — about 60 times faster than the looping method.

105 thoughts on “Writing To A Range Using VBA”

  1. The same concept is also efficient for modifying large blocks of data on a sheet
    such as using the Trim function on many cells.

    Sub test1()
    Dim myRange
    Dim lngctr As Long
    myRange = Range(“A1:A10000?).Value
    For lngctr = LBound(myRange) To UBound(myRange)
    myRange(lngctr, 1) = Trim(myRange(lngctr, 1))
    Next
    Range(“A1:A10000?).Value = myRange
    End Sub

    Sub test2()
    Dim myRange As Range
    Dim lngctr As Long
    For Each myRange In Range(“A1:A10000?).Cells
    myRange.Value = Trim(myRange.Value)
    Next
    End Sub

  5. This works great for data… any ideas on cell formatting? I have a script where I read all data and cell formats into an array, sort the array, then put the data and formats back on the sheet. I would like to use the assign array to sheet method but I have to put the formats on as well and they are considerably slower using the cell by cell method!

    Kevin

  6. Is there a way of transfering one portion of the array to one worksheet? For example, copy the first three columns to one sheet and the rest to another sheet.

    Thanks for the great tips, John.

    Huaming

  8. Kevin –

    If discontiguous collections of cells need to have the same formatting applied, you could build up a range using Union(), and then apply the particular formatting to all affected cells in one step. A bit more coding, but if it is reduces the perceived wait, it’s probably worth it.

  9. Huaming –

    Between reading and writing, put a loop that splits TempArray into TempArray1 and TempArray2, then dump each into the respective worksheets.

  10. Hi, Jon,

    I made two arrays (DataArray1 and DataArray2) by spliting a big one as you said and dumped them into a chart by using:

    ActiveChart.SeriesCollection.NewSeries
    n = ActiveChart.SeriesCollection.Count
    ActiveChart.SeriesCollection(n).XValues = DataArray1
    ActiveChart.SeriesCollection(n).XValues = DataArray2

    Somehow the chart would not take these two arrays, while I can paste them onto a worksheet. Is there something special that I have to do when transferring array into a chart? I’d like to use the array technique for charting to avoid showing large area of data on the worksheet.

    Thank you very much and best regards,

    Huaming

  11. Huaming –

    What you have to do is make sure the array is less than about 253 characters long when written like this:

    ={1.234567890123,2.345678901234,3.456789012345}

    This means truncating as many characters as necessary:

    ={1.234,2.345,3.456}

    Even with truncating, your limit is around 125 points, which assumes single digit integer values. Not very useful. Go one step further and dump the arrays into the worksheet, and point the chart series at these ranges.

  12. Hi, Jon,

    Indeed the series that I made using the array techniques exceeded way too much than the 253 characters limits. It worked fine when the data are transferred into a worksheet and have the chart to retrieve data from there. It is just a little inconvenient to have a lots of data showing up on the sheet. I have to put them away from the viewing area.

    Thank you very much for your timely and on-the-spot advice.

    Huaming

  13. Use a hidden sheet for the chart data.

    I seem to have fielded this question about a dozen times in the past week on various forums. Maybe I should write up a better summary on my site, so I can just point people to that.

  14. Please DO write up a “cheat sheet” summary for this, Jon. I have learned quite a few of charting tricks from you. My most favorite one is how to make “scientific/exponential notation (with superscript formating)” labels on X-Axes. I earned a cup of Star Bux coffee by showing it to my colleague. Not any more. We always appreciate your good work.

    Huaming

  15. I had someone else’s code last week that copied a large amount of data from an Access query to an Excel sheet. 11,000 records, 10 fields/ record. He did it field by field, line by line. Needless to say, this took over 20 minutes to run.

    I rewrote it using DAO’s recordset GetRows method, which can copy the entire results of a query into a variant array. After transposing it (couldn’t find a shortcut for this), I just plunked it into a range using your same method. Result: it takes less than a minute now. Sweet!

  17. The amount of difference between the two timings will increase as you add additional calculations to the spreadsheet as well. Especially if you are writing to a range that is used in other calculations in the workbook. Did you try a timing on the first loop with recalculation turned off until after the entire range has been posted? I’m not sure if it will make much of a difference in an otherwise empty workbook…

  18. Thanks JoeS for comment 1. I have been using this technique for a long time to write or read large blocks of data to an Excel sheet but I never thought about using it in other contests as well. Thanks for helping me think outside the box!

  20. I just discovered the same issue in passing arrays to an Excel Chart Object using “.XValues =” and “.Values =”, during my first Excel VBA project (have used QB45 & VB6 for years). Jon’s explanation of a 253 character limit makes sense of my tests where I hit different limits (from 15 to 117 pts) depending on what the exact values were (# characters in the numbers). Not sure why I found no explanation on MSDN. As Hauming, I would prefer not passing values via a worksheet, both for speed and simplicity, but will use a hidden area for now. Microsoft KB #139401 article discusses the inverse problem of reading existing Chart values into an array. You must transpose the receiving array or define it as a 2-D array ValOut(npts,1). I tried several variations on this theme to input values, but no luck.

  21. Nitesh – in a word, yes.

    Declare the function as a variant

    Redim it to the required size

    Put your data in the array

    finally:

    MyFunction = MyArray

    Enter the function as an array formula, or use Index() to access a sub-set of the elements.

  23. I am trying to create a chart with 1 set of x-values, but 2 different y values so I have two data sets. The problem is that at a given x-value, one of the y-values produces an error while the other does not. If I chart the two lines, any y-values with this error, “#N/A N.A.”, will show up as zero on the chart, hence ruining any trendline. How do I NOT include these data points that have errors using code?

  25. Also, be aware that there are limitations of passing arrays to Excel ranges, including using the built-in Transpose function. See http://support.microsoft.com/kb/177991 for more information. Anyone who considers himself an Excel programmer should read this article as it also touches on some general concerns about data types, as well as using Excel through COM.

    I am getting a type mismatch error when trying to transpose an array created by GetRows, which is strange because it is only 12 x 713 in size, and an array 21 x 23040 from a different recordset transposes just fine, using the exact same code (it’s just a different query). I can’t quite figure it out. I think I’ll have to write my own transpose function using the CopyMemory Windows API function.

  26. John,

    That’s a very useful trick you posted – thanks! Can a similar trick be used to quickly fill a range with formulae? jkpieterse says “AFAIK, you can only use the Formula and the Value property to bulk-read and write to/from an array”. I tried the Formula property, by altering your code as follows:
    …
    Dim TempArray() As String
    Dim CurrVal As Long
    Dim CurrValAsString As String
    …
    For i = 1 To CellsDown
    For j = 1 To CellsAcross
    CurrValAsString = “=” & CurrVal
    TempArray(i, j) = CurrValAsString
    CurrVal = CurrVal + 1
    Next j
    Next i
    …
    TheRange.Formula = TempArray

    This (on Office Excel 2003), didn’t update the formulae, but just put the string values into the cells. So A1 became “=0?, and so on.

  27. Jocelyn –

    If each cell in the range has the same formula, .Formula applies the formula as a formula. If the cells receive different formulas, .Formula works like .Value, entering the formula as text. You need to replace all = in the range with =, which re-enters the formulas as formulas:

    Sub SimpleFmla()
      Selection.Formula = “=row()+column()”
    End Sub

    Sub TrickyFmla()
      Dim sFmla(1 To 3, 1 To 3) As String
     
      sFmla(1, 1) = “=row()+column()”
      sFmla(2, 1) = “=12/Row()”
      sFmla(3, 1) = “=1/row()/column()”
     
      sFmla(1, 2) = “=sin(row()*pi()/180)”
      sFmla(2, 2) = “=4^3”
      sFmla(3, 2) = “=ln(10)”
     
      sFmla(1, 3) = “=cos(row()*pi()/180)”
      sFmla(2, 3) = “=sqrt(2)”
      sFmla(3, 3) = “=na()”
     
      With ActiveCell.Resize(3, 3)
        .Formula = sFmla
        .Replace “=”, “=”, xlPart
      End With
     
    End Sub

  28. Jocelyn,

    It works fine if you define the array of formulae as a variant, then you dont need to replace = with =

    so in Jon’s example use

    Dim sFmla(1 To 3, 1 To 3) As variant

  29. Charles –

    “Dim sFmla(1 To 3, 1 To 3) As Variant”

    Doh! I knew I’d done this without the .Replace “=”, “=” piece, but I couldn’t recreate it on the fly.

  30. With ActiveCell.Resize(3, 3)
    .Formula = sFmla
    .Value = .Value ‘This jsut works fine.
    ‘.Replace “=”, “=”, xlPart
    End With

  31. Davy –

    Since the formula is only in the cell as a text string, .Value=.Value reenters the formula, the same way that replacing the equals sign does. I suppose someone could go through to see which approach (.value=.value, replace =, variant array) is fastest, but I’ll just use the variant array, because it’s easiest.

  32. Just to point to a limitation of the array method: no element can have more than 911 characters, if it has the write stops at that point.

    See:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;818808&Product=xl2003
    You may receive a “Run-time error 1004? error message when you programmatically set a large array string to a range in Excel 2003
    This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.

    http://support.microsoft.com/?scid=kb;en-us;832136
    Data may be truncated when you transfer array data to cells in an Excel worksheet
    This problem may occur when one of the following conditions is true:
    • In Excel 2007, the VBA array is longer than 8,203 characters in length.
    • In Excel 2003 and in earlier versions of Excel, the VBA array is longer than 1,823 characters in length.
    To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time.
    166342 (http://support.microsoft.com/kb/166342/) Description of the limitations for working with arrays in Excel

  33. “To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time.”

    Kind of negates the speed benefits of array-to-range, doesn’t it?

    I can think of an alternative, though. Make a second array the same dimensions as the first. Populate array 2 with the long elements, and remove them from array 1. Dump array 1 to the range as before, then only populate the cells that have long elements in array 2.

  34. “Dump array 1 to the range as before, then only populate the cells that have long elements in array 2.”

    Yes, the computation time to skip long elements is less than the paste time.

    I did some speed tests and after saving time by
    .calculation=xlmanual
    .screenupdating=false

    the array method is still about 20 times faster than writing individual cells. The times vary randomly a lot, which I presume is because so many background tasks are going on as well in the PC. I’d need to prepare a special test PC for real testing, but the difference is still clear enough.

  35. Can someone help me to understand what it wrong with the following code? It outputs all 0’s. I’ve copied the code at the top of the page here, and it works fine. But when I alter it to my own needs, it doesn’t work. I know it’s something simple, but my simple mind is still lacking.

    Sub temp()
    Dim numbers(100) As Single
    Dim i As Integer
    Dim therange As Range

    Set therange = Range(“A1:A100?)

    For i = 1 To 100
    numbers(i) = Rnd * 100
    Next

    therange.Value = numbers
    End Sub

  36. skirby: Arrays that you write to ranges must be two dimensions: rows and columns.

    Sub temp()
        Dim numbers(1 To 100, 1 To 1) As Single
        Dim i As Integer
        Dim therange As Range
       
        Set therange = Range(“A1:A100”)
       
        For i = 1 To 100
            numbers(i, 1) = Rnd * 100
        Next
       
        therange.Value = numbers
    End Sub

  37. Nevermind. I figured it out. Since arrays in Excel are horizontal, I had to transpose the array to get it to populate the range of cells correctly. This is a great site, though.

  38. Thanks for the fast response. The following also works:

    Sub temp()
    Dim numbers(1 To 10) As Single
    Dim i As Integer
    Dim TheRange As Range

    Set TheRange = Range(“A1:A10?)

    For i = 1 To 10
    numbers(i) = i
    Next

    TheRange.Value = Application.WorksheetFunction.Transpose(numbers)
    End Sub

  39. Does this work for array of string? I tried but didn’t work. Is there a way to get around without using loop.

    Thanks,

  42. If you are working with recordsets:
    Put the whole recordset into multidimentional array in one step…this is at least 10 times faster than doing using a recordset to get data in a loop..

  43. Hi, can someone tell me wht is wrong with my code (see below). I am trying to use the above example of filling an array within VBA and then later pasting it into Excel.
    So far, it only deposits cells full of zeros!
    I am a newby at VBA:)

    Sub Plot_kc_disp()
    ‘this will enable plotting of kc/km with displacement using the Newmark sliding block program
    ‘
    Dim kc As Single ‘critical acceleration
    Dim km As Single ‘maximum acceleration (ie PGA)
    Dim R As Integer ‘Row number
    Dim MaxD As Single ‘Max displ
    Dim myRange As Range ‘output range for results
    Dim StartTime As Double ‘timer
    Dim TempArray() As Double ‘temporary array

    R = 1
    kc = 0
    ‘speed tweaks
    Application.ScreenUpdating = False

    Let km = Worksheets(“Input data”).Range(“G8?)
    ‘ Record starting time
    StartTime = Timer
    ‘ Redimension temporary array
    ReDim TempArray(1 To 300, 1 To 2)
    ‘ Set worksheet range
    Worksheets(“Output Sheet”).Select
    Set TheRange = Range(Cells(1, 1), Cells(300, 2))

    Do While (kc / km)

  46. Can anyone tell me whats wrong with this code..I cant seem to get all the values of array a. the code results in a(1) being written a 100 times..

    Thanks

    Sub test()
    Dim a() As Variant
    Dim ranger As Range
    For i = 1 To 100
    ReDim Preserve a(1 To i)
    a(i) = Rnd()
    Next i

    Set ranger = Worksheets(“Sheet1?).Range(Cells(1, 1), Cells(100, 1))
    ranger.Value = a
    End Sub

  47. Sree

    The problems is that if you declare a one dimensional array it is treated as being equivalent to a row rather than a column.

    There are two ways you can fix your code; either make the range 1 row x 100 columns, or declare the array as 100 x 1.

    So either change the set ranger line to:

    Set ranger = Worksheets(“Sheet1?).Range(Cells(1, 1), Cells(1, 100))

    or change as below:

    Dim ranger As Range
    ReDim Preserve a(1 To 100, 1 to 1)
    For i = 1 To 100
    a(i,1) = Rnd()
    Next i

    Note that the redim needs to be outside the For Loop, because you can only redim preserve the last dimension, but unless there is some reason why you need to redim every cycle of the loop, that is the better place for it to be anyway.

  49. At what point does this method become worthwhile? I am only dealing with 30 values, would I see an improvement? Just as some background, my behemoth spreadsheet is used (partly) for marking an attendance roll and writing a single character to each of 30 cells seems to be taking *forever*. I’m using Excel 2007.

  50. Debra – if writing to 30 cells individually is taking forever then you should see a huge improvement writing them in one go as an array.

    At what point does it become worthwhile? I’d say 2 or more cells.

  51. I’m with Doug. If I need to read or especially write more than a single cell, I write extra VBA code (which executes rapidly) to create an array to minimize range access operations (which execute slowly).

  52. It can be a different animal using String arrays.
    TheRange.Value = TempArray can take as long as writing to each cell.

  53. The funny thing about this thread is that you’ve got people who make their living writing VBA vs. people who make their living easier using VBA. I am sadly among the latter group, and the difference is pretty significant. When your job involves delivering a polished end product, and speed is absolutely a component of that, I agree with Jon completely. But the more common use of VBA is taking a manual process and automating it. That fact alone makes it at least 10x faster and probably 100 – 1000x faster. If spending 3x as long yields a 10,000 performance gain, it is unnoticed, unappreciated, and not worth the time for most people. Personally, unless I’m dealing with a maddeningly slow range write (i.e. it’s still not done once I’ve refilled my coffee), it’s not worth the effort to make it marginally faster.

    To put it another way…two hours of coding to save 30 minutes once a week pays off in a month. 3 hours of coding to save 30 1/4 minutes once a week takes a month and a half. I could also add that that extra hour of coding is nowhere near as fun or rewarding as the magic that happens during the first 2. Anything that takes the fun out of coding is counterproductive in the long run.

  54. zach – I’m in the same position as you; all my programming is to get my engineering done quicker/better, and almost all of it is only used by me, but I really don’t see where the extra time comes in. If you get used to using arrays and writing them in one operation to the spreadsheet the difference in coding time is negligible, in fact it would probably take me longer to do it any other way, because I’d have to think about how to do it.

    The other thing is that old spreadsheets of mine that write one cell at a time are really annoying, even if they only waste a few seconds each time.

  55. Doug is exactly right. Once you absorb a “best practice”, you tend to use it again and again. It doesn’t cost anything when you reuse it, because you do it right the first time. I’ve been writing VBA professionally for over five years, but I have been using arrays to exchange data with the worksheet for over ten years. I’ve been avoiding the Object.Select/Selection.Action code found in recorded macros for almost as long.

  56. These kinds of discussions would be moot if one were to follow the “best best practice,” that being to modularize code.

    Some may have read or heard about the concept of a “data abstraction layer” in the context of interacting with a database. Well, an Excel range *is* a database. A long time ago I wrote an abstraction layer for my code to interact with an Excel range. By modularizing this code, I don’t have to debate about best practice or coding time or debugging overhead. The abstraction layer works. I don’t have to test it. It becomes a case of ‘drop and use.’

    As an aside, there are many routines that I never test when I use them. All the code to create a menu for an add-in is ‘drop and use.’ All the code to distinguish between RibbonX and non-RibbonX environments is ‘drop and use.’ All the code to implement help or link to my website or show an up-to-date copyright statement or… or… or… is ‘drop and use.’ If you asked me how one of my routines worked, I’d have to shrug and reply “I don’t know. The last time I looked at the code was 5 years ago – if not longer.”

    Below is a simplified version of the 2 routines that constitute the abstraction layer. Since I simplified the actual code for illustration purposes, I may have introduced some glitches but hopefully not.

    The mapInput function takes an Excel range and returns a 1D array. The mapOutput function takes a 1D array and returns either a 1D array or a Nx1 2D matrix.

    Drop the 2 functions into any project and the UDF (or subroutine) can now “outsource” its I/O process.

    Option Explicit
    Option Base 0

    Function mapInput(X)
        If Not TypeOf X Is Range Then
            mapInput = X
        ElseIf X.Columns.Count > 1 Then
            If X.Rows.Count > 1 Then
                ‘don’t handle 2D matrix
            Else
                With Application.WorksheetFunction
                mapInput = .Transpose(.Transpose(X.Value))  ‘should it be value2?
                    End With
                End If
        ElseIf X.Rows.Count > 1 Then
            mapInput = Application.WorksheetFunction.Transpose(X.Value)
        Else
            Dim Rslt: ReDim Rslt(0): Rslt(0) = X.Value: mapInput = Rslt
            End If
        End Function
    Function mapOutput(X)
        Dim myCaller
        On Error Resume Next
        Set myCaller = Application.Caller
        On Error GoTo 0
        If Not TypeOf myCaller Is Range Then
            mapOutput = X
        ElseIf myCaller.Columns.Count > 1 Then
            mapOutput = X
        Else
            mapOutput = Application.WorksheetFunction.Transpose(X)
            End If
        End Function

    What does the UDF I want to write look like? It’s modularized.

    Function myUDF(inX)
        Dim Arr
        Arr = mapInput(inX)
        Arr = processUDF(Arr)
        myUDF = mapOutput(Arr)
        End Function

    In fact, if one wanted to dispose with the Arr variable, one could write a 1 line UDF!

    Function myUDF(inX)
        myUDF = mapOutput(processUDF(mapInput(inX)))
        End Function

    and processUDF would be the main code to process a 1D array and return a 1D array. For illustration purposes I wrote a trivial routine, which, with its one support function, looks like:

    Function ArrLen(X)
        On Error Resume Next
        ArrLen = UBound(X) – LBound(X) + 1
        End Function
    Function processUDF(inArr)
        Dim I As Long
        Dim Rslt: ReDim Rslt(ArrLen(inArr))
        For I = LBound(inArr) To UBound(inArr)
            Rslt(I – LBound(inArr) + LBound(Rslt)) = inArr(I) * 2
            Next I
        processUDF = Rslt
        End Function

    Note the seemingly overly complicated assignment to the Rslt() element. To me, it’s basic defensive programming that means I don’t care how the 2 arrays are declared. The code adjusts itself to the respective array bounds.

    With the above code, I don’t care how the UDF is used. It works if the input is a 1D row (A1:C1 for example), a 1D column (A1:A3), a single cell (e.g., B2), or a constant array, such as {1,2,3}. The simplified version does not work with a column array, i.e., {1;2;3}.

    I also don’t care if the function is array-entered in a single row (C2:E2 for example) or a column (C2:C4) or, for a single cell as the input, in a single cell.

    The above function (i.e., myUDF) can also be called from a subroutine as in the two examples below:

    Sub callFromSub()
        Dim Arr: Arr = Split(“1,2,3”, “,”)
        Arr = myUDF(Arr)
        MsgBox (Join(Arr, “,”))
        End Sub
    Sub callFromSub2()
        MsgBox (Join(myUDF(Split(“4,5,6”, “,”)), “,”))
        End Sub

  57. As a follow up to my previous post, what if one had a function with 2 or more parameters? The modularized approach means the code is trivial to extend.

    Function processUDF2(In1, In2)
        Dim I As Long
        If ArrLen(In1) <> ArrLen(In2) Then Exit Function
        Dim Rslt: ReDim Rslt(ArrLen(In1))
        For I = LBound(In1) To UBound(In1)
            Rslt(I – LBound(In1) + LBound(Rslt)) = _
                In1(I) * In2(I – LBound(In1) + LBound(In2))
            Next I
        processUDF2 = Rslt
        End Function
    Function myUDF2(In1, In2)
        myUDF2 = mapOutput(processUDF2(mapInput(In1), mapInput(In2)))
        End Function

    or with myUDF2 broken up into discrete steps:

    Function myUDF2(In1, In2)
        Dim Arr1, Arr2
        Arr1 = mapInput(In1): Arr2 = mapInput(In2)
        Dim Rslt: Rslt = processUDF2(Arr1, Arr2)
        myUDF2 = mapOutput(Rslt)
        End Function

  58. Can I use this to export the content of an array to a comma delimited text file which can be saved and emailed, then imported into the same structure? I currently do this by writing a range cell by cell looping line by line (about 10,000 rows and about 15 columns), for example:

    Set Cells = Workbook.Sheets(“Coding”).Range(“VILLAGES”).Columns(1)
    For Each Cell In Cells.Cells
    If Cell.Value = 1 Then
    Write #1, _
    Cell.Offset(0, 1).Value, _
    Cell.Offset(0, 2).Value
    End If
    Next Cell

    which is quick to export, but can be a bit slow to import on some of the older computers being used. So I’m wondering if it can be put into an array to make it faster?
    Some of the content is numbers, some dates, some letter codes, and some words. Currently I Dim all items for import as String, but wondering if I should change some to Long if it is only a number? I note:

    Jim Cone says:
    October 22, 2009 at 1:37 pm
    It can be a different animal using String arrays.
    TheRange.Value = TempArray can take as long as writing to each cell.

    Any advice to speed it up is most welcome!

  59. Hi everyone,

    I have some problem with reading from range to array. Because my data is too big (20 million cells in total). Does anyone know how to read this data without using the loop to read each cell. Thank you very much.

  61. Hi have two excel sheet as input and have to get one excel sheet as
    output.

    In the output excel sheet I have header defined.
    header fileds are :- “feature name”, “doc_type”, “size”, “review_id”.

    From other two excel sheet I have to get the all headers values for multiple documents and have to fill in final excel sheet.

    Please help me how to do it ??

  62. >> Erik Eckhardt says:
    >> February 22, 2007 at 11:55 am
    >> It’s not Application.Transpose. It’s
    >>
    >> Application.WorksheetFunction.Transpose(vaMyArray)

    So why does “Application.Transpose” work for me?

    Zorba

  63. All of the Worksheet function methods, while technically children of the WorksheetFunction object, have a direct link to Application and can be called both ways. It’s similar to how you can call Worksheets from the Application object, even though Worksheets are children of the Workbook object.

  64. There is at least one difference between calling though the WorksheetFunction object vs. directly through the Application object. Calling this:

    Application.Worksheetfunction.Index([{1,2,3}],4)

    raises an runtime error (1004), but calling this:

    Application.Index([{1,2,3}],4)

    just returns #REF!. Bascially, regarding errors, the non-WorksheetFunction versions behave more like the Excel worksheet functions they expose.

    (I know Dick and most of his readers probably already know this, but it’s a distinction that I find useful, so I thought I’d mention it…)

  65. Hello – I’m still stuck in the early stages of understanding how this works. I’ve tried to apply it in a particular subroutine, but the range gets filled with zeros. I’m a very primitive VBA coder :) – any help much appreciated.
    The code, without the garbage goes like this:

    SUB
    Dim numLev() as integer
    ‘
    …
    Redim numLev(12,1)
    ‘
    ..
    loop during which the twelve levels are given values (1 to 12 as it happens)
    …
    Range(“C21:C32?).value=numLev
    …
    END SUB

  66. Alison –

    This works for me:

    Sub test()
    Dim numLev() As Integer
    ReDim numLev(1 To 12, 1 To 1) As Integer
    Dim i As Integer

    For i = 1 To 12
       numLev(i, 1) = i
    Next i

    Worksheets(“Sheet1”).Range(“C1:C12”) = numLev
    End Sub

    …mrt

  67. Alison,
    To build on what Michael said…

    The reason you get a range of zeroes is because of your ReDim statement. The default for VBA is to have zero-based arrays (unless you change the option). Thus, you’ve actually dimensioned the array as:
    numLev(0 to 12, 0 to 1)

    This array actually has two columns. So when you try to display the array on the worksheet, you’ve only assigned a range with one column. So the displayed data gets pulled from the zero-column of the array, which is empty (zeros). If you want to see your whole array, zeroes and all, you could use
    Range(“C21:D33?).value=numLev

    Better yet, dimension the array as Michael showed.

    -Josh

  68. Michael and JoshG
    Thank you so very much for the correction and explanation as to what had been done wrong.
    Very much appreciated.

    Wish I’d known about this at least a year ago, I’m sure my code would all be running much faster… Don’t think I can face searching for all the possible places where this could be applied!

  69. Hi there,

    Can someone help me figure out how to code in vba to set write login time logout time in a specified range and to prevent the other toggle button from being executed when the specified range is marked as “X”. thanks for your assistance.

    jov

  71. I was trying to write a 1D array to a column in an Excel worksheet. I couldn’t figure out why the first value in the array was being written N times, until I found this webpage and used the Transpose function. Thanks to “skirby” for that.

  72. Trying to see if i can get a little help with vba codeing, what i am trying to do is have an email sent to my outlook and a coworker’s email when a cell changes value. Below is a code that i have so far but not sure how to select the colum row that changes the value.
    Sub Mail_small_Text_Outlook()
    ‘Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    strbody = “Hi there, this message was sent to you from Excel” & vbNewLine & vbNewLine & _
    “”

    On Error Resume Next
    With OutMail
    .To = “My addy”
    .CC = “coworkers addy”
    .BCC = “”
    .Subject = “This is the Subject line”
    .Body = strbody
    .Display ‘or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub

    I want an email sent when the value changes in “T” colum. Any help would be great.Thanks
    *Note there are many cells that will change i.e. 10/1/2009 will change to Due

  74. Hi all,

    Firstly – great post John.

    I’m wondering if this same Array -> Range approach will work with a non-contiguous range?

    For example we have an application at work that needs to write around 400 values into an Excel sheet (it does this via ActiveXObjects from the browser. Yuck, I know). At present it does this in a loop calling .cell(row, column) and setting the value property.

    I imagine doing Range(“A1, B10, C15, etc”).value = SOME ARRAY, would be much quicker but I’ve been hunting the web for a while now and haven’t found anyone who has done this. Perhaps it’s just not possible?

    Be interesting to hear any thoughts.

    Cheers

  76. WOW !!!!!

    TOTALLY AMAZING !!!!!!!!!

    I had 100,000 rows of data with 13 columns.

    I used a loop to read from an array to the cells.

    It took 25 minutes.

    I used application.screenupdating = false and it didn’t make a significant difference at all. It still took around 25 minutes, maybe a couple of minutes sooner, but not much.

    I used the technique you showed to move from an array to a range of cells and the total time it took was 1 second.

    I doubled the rows to 200,000 and the total time it took was 2 second.

    I no longer have to worry about whether I want to show the details or not.

    I show them every time.

    Thank you for this extremely valuable information.

  77. I am writing code to take data from three different spreadsheets and add the values for each cell from B2 to K11. Then I add the columns and the rows. This required a fairly long piece of code. I would like to write a do loop to do this, but am not sure if I need to treat the data as an array.

  78. End With ‘alternatively for speed, the above could be written to the range in a single block ‘(see Daily Dose of Excel Blog Archive Writing To A Range Using VBA) ‘”For each” item in a collection is usually faster than using an index ‘(see Optimize VBA)

  79. Hello,

    I have tried adapting this code to a subroutine of mine that uses a select case loop. I also only return zeros when I run my program. I am new at vba so I don’t know where to start on where I am going wrong.

    Here is my adaptation: (Where Column L is supposed to populate with values based on the value of column J)

    Sub VariableIndicatorCode()
       
        Dim Store1 As String * 7
        Store1 = “76####1”          
        Dim Store2 As String * 7
        Store2 = “76####3”
        Dim Store3 As String * 7
        Store3 = “76####5”
        Dim LR As Long
        LR = Range(“B” & Rows.Count).End(xlUp).Row
        Dim CellsDown As Long, CellsAcross As Long
        Dim i As Long, j As Long    
        Dim TempArray() As Double
        Dim TheRange As Range
       
        CellsDown = LR
        CellsAcross = 12

        ReDim TempArray(2 To CellsDown, 12 To CellsAcross)
        Set TheRange = Range(Cells(2, 12), Cells(CellsDown, CellsAcross))
       
        For i = 2 To CellsDown
            For j = 12 To CellsAcross

                Select Case ActiveCell.Offset(0, -2).Value
                    Case Store1: TempArray(i, j) = “1”
                    Case Store2: TempArray(i, j) = “2”
                    Case Store3: TempArray(i, j) = “3”
                End Select

             Next j        
        Next i

        TheRange.Value = TempArray

    End Sub

    Does anyone have any ideas?

    Thanks,

    Mark

  80. Mark:

    I think you have a couple of problems. First, you are relying on the the Activecell, specifically the value in ActiveCell.Offset(0,-2). But the active cell doesn’t ever change in your code. Your never finding a match so you’re getting all zeros.

    Also, your store numbers make me think you’re want to compare using Like, so that 7623231 would be true for 76####1, but you’re not comparing with Like. Even if your activecell offset returned a store, it wouldn’t be a match.

    You dim TempArray as Double, but fill it with Strings. Excel will convert, but if you were more explicit, it would be better.

    Here’s how I might write that code

    Sub VariableIndicatorCode()
       
        Dim vaStore As Variant
        Dim lLastRow As Long
        Dim rStores As Range
        Dim rCell As Range
        Dim aOutput() As String
        Dim lRow As Long
           
        ‘Put the store number masks in an array, just for brevity
        vaStore = Array(“76####1”, “76####3”, “76####5”)
       
        ‘Find the range of store numbers in column J
        With Sheet1
            Set rStores = .Range(“J2”, .Cells(.Rows.Count, 10).End(xlUp))
        End With
       
        ‘Make my output array the same size as the store number list
        ReDim aOutput(1 To rStores.Cells.Count, 1 To 1)
       
        ‘Loop through the store numbers
        For Each rCell In rStores
            lRow = lRow + 1
            ‘Use Like to compare the store number to the mask and populate the array
            Select Case True
                Case rCell.Value Like vaStore(0): aOutput(lRow, 1) = “One”
                Case rCell.Value Like vaStore(1): aOutput(lRow, 1) = “Two”
                Case rCell.Value Like vaStore(2): aOutput(lRow, 1) = “Three”
            End Select
        Next rCell
       
        ‘Write the array to a range
        rStores.Offset(, 2).Value = aOutput
       
    End Sub

  81. Dick,

    Thank you for your response. I tried copying and pasting your code but I was not able to get it work (although I only tried a couple of times). I was able to fix mine to work by changing the line:

    Select Case ActiveCell.Offset(0, -2).Value

    TO:

    Cells(i, j).Offset(0,-2).Value

    I didn’t do anything with the store numbers because I actually put the # signs in before posting, I have actual numbers in my subroutine.  I also changed what I am filling my array with to number values instead of strings, per your suggestion.

    Again, your response was very much appreciated.

    Mark

  82. I need a macro or script that an Outlook rule can execute. I need to Export Outlook E-mails to a particular folder as Tab delimited files. The Export function in Outlook does exactly what I need but I need it to happen automatically whenever the user sends or receives an E-mail.

  84. In Excel 2010, pasting range from vba works for upto 32,767 characters. (32,767 is the cell’s character limit to hold)

    And, if the text is longer than 32,767 characters, say 50,000… it will paste the first 32,767 in the cell (cell limit) and continue the rest of the pasting… The paste will just work normally for all the other cells (unlike Excel 2003, where the paste breaks at this point and the rest of the cells are left BLANK).

    So, I would say this problem is fixed in Excel 2010.

  86. When pasting to a Range from Array, none of the individual array elements may have a text string greater than 8203 characters, otherwise the method fails with run-time error 1004, pasting data up to the cell that booms into this limit.

    If using Application.Transpose() -or Application.WorksheetFunction.Transpose() which is exactly the same- then the limit is reduced to only 255 characters per array element.

    These same 8203 field size limit applies to CopyFromRecordset method.

    Tested on Excel 2007.

    Dutch

  87. Pingback: VSTO & VBA – How to troubleshoot Excel memory and performance problems caused by inefficient code loops | MSDN Blogs

  88. Hi !
    First, a big thanks for the limitation info (8203 characters) on Excel 2007, i’ve been searching for this everywhere.
    I’ve made a little modification to my script that crop the values if they’re > 8000

    Now my array to range is working correctly but it’s so sloooooow.
    To be clear, my data is full of long strings (8000 lines with some colums having 5000+ characters including backspaces (Chr(10)) in it.
    When excel paste the data into the range, autoformatting sets the cells on wraptext = true.

    Here’s the code :

    I’ve tried to play with wraptext as you can see, but no effect (same with rowheight)

    Any idea of how i could get rid of this wraptext to speed things up?

    (Apologies for bad english, i’m french)


  89. Sub M_snb()
    Application.screeenupdating =false

    sheets("resultat").columns(1).specialcells(4).entirerow.delete
    sn=sheets("resultat").cells(1).currentregion

    for j=1 to ubound(sn)
    for jj=1 to ubound(sn,2)
    sn(j,jj)=mid(sn(j,jj),application.min(8000,len(sn(j,jj))))
    next
    next

    sheets("resultat_001").cells(1).resize(ubound(sn),ubound(sn,2))=sn
    application.screenupdating=true
    End Sub

  90. 1 mistake:
    Sub M_snb()
    Application.screeenupdating =false

    sheets("resultat").columns(1).specialcells(4).entirerow.delete
    sn=sheets("resultat").cells(1).currentregion

    for j=1 to ubound(sn)
    for jj=1 to ubound(sn,2)
    sn(j,jj)=left(sn(j,jj),application.min(8000,len(sn(j,jj))))
    next
    next

    sheets("resultat_001").cells(1).resize(ubound(sn),ubound(sn,2))=sn
    application.screenupdating=true
    End Sub

  92. Pingback: help fix code from out of memory error

  93. Can anyone help me outline the VBA code required for the following task:

    1) imagine a grid of regularly spaced points centered at X=0, Y=0. Let’s say the spacing between points (in both X and Y directions) is 10, and the range goes from -1000 to +1000 for both X and Y (i.e. 201 points in each direction)
    2) I would like to get the X,Y coordinates for all points put into the A,B columns.

    Any suggestions would be appreciated.

  97. I just got bit with an undocumented Excel (using 2010) bug with this approach. If the range you’re copying the variant array into includes hidden columns, the hidden columns “reset” the array index from which the new value is given. For example, if you have range A1:C1, but B is hidden, and you write its Value to a Variant array of size (1, 3), the variant array’s value for (1,1) is used for both A1 and C1.

  98. I could not reproduce that bug in 14.0.7015.1000 (32 bit) using this code:

  99. Hi,
    Sometimes ranges are defined like this:
    Range(“A1:A100″)
    and at other times like this:
    Range(“A1:A100?)

    What is the effect of the question mark as opposed to the quote?
    Thanks

  100. @Crusty: It’s not related to VBA – those question marks should be double quotes. When you type double quotes in a WordPress blog comments, it turns them into fancy quotes. You can see in your comment how they are stylized, but you almost certainly didn’t type them that way. Then, for some reason, your browser (and mine) doesn’t know how to display the stylized quotes that point Northeast. When and HTML renderer doesn’t understand a character, it replaces it with a question mark. If you use code tags or pre tags, the quotes should remain as normal double quotes.

    Raw:
    Range(“a1:a100”)

    Pre:

    Code:

  101. Hi, I have my Excel sheet linked to an external data feed platform (RTD).

    I have many cells which have formula based on the values of the data feed. Sometimes i need change the formulaes, and retype them but those cells which contain formula will display #N/A for a few second before everything settles down an run as per normal. This is normal behavior.
    Manually i have no issues typing the formulaes. Excel accept my typing and shows a “#N/A” error till the rtd server start send data.

    The issue occour when a try a programmatic way to write the formulaes.
    The problem I encountered is that, whenever I try write the new formula in a cell (using array or individual cell) this arise a error (1004 + 2042 ) and this dont let’me write down the formula string to the cell.
    Just what i need is that excel let me write the formula.
    Someone here know a way to solve this?
    …
    dim stock as variant
    stock = “=RTD(“”MY.rtdserver””;;””NEGS””;””DOLFUT””;””Prc””)”
    wsr.Cells(2,2) = stock
    …
    This will arise a error just cuz the rtdserver is not active at the moment

    If manually i cut and past that same string ( =RTD(“my.rtdserver”;;”NEGS”;”DOLFUT”;”Prc”) with simple cotes) to a cell, i have justa a #N/A error till the rtd server send data.

  102. I don’t know much about RTD. What if you wrote

  103. I want to use the code using formula, but I am having trouble implementing. would you help please?

    I want to evaluate the formula below and paste the values to a range of the same size as the ranges referenced in the formula
    I have a formula of the form like this form:
    =IF(AJ8=0,AH8,SUMIF(ReinsPolNumRange,$M8,ExpecPremRange)*AJ8/SUMIF(ReinsPolNumRange,$M8,ActualPremRange))

    with an average of 100,000 rows, things are extremely slow. I have a feeling this code is exactly what I need, but I literally spend the last 6 hours trying to figure out and can’t.

    thanks,

    Wilnex.

  104. Hello,

    Can this “copy to an array then copy back” approach work, if the original loop deleted rows based on certain values?

    Original code:

    Do Until iCurrRow > iLastRow
    If Cells(iCurrRow, 12) = 0 And Cells(iCurrRow, 13) = 0 Then
    Rows(iCurrRow).EntireRow.Delete
    iLastRow = iLastRow – 1
    Else
    iCurrRow = iCurrRow + 1
    End If
    Loop

    I am not sure how to perform the delete operation on the array element and have it resize without getting quite complex.

    Any help much appreciated. Thanks for the very useful posts.

  105. Thanks for sharing this great stuff!!! I am wondering how the same technique can be applied accross a range, where different conditions apply for several columns

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.