+ Reply to Thread
Results 1 to 3 of 3

poor macro performance

  1. #1

    poor macro performance

    I have a macro to read in the a comma seperated value (CSV) file and
    set the data into a named range. The file format is
    <range_name>,<numeric value>. The problem is that the macro takes about
    8 minutes to read 34000 records and seems to gradually slow down.
    This would lead me to believe I have a memory leak of some kind. Most
    of the time is split evenly between two functions
    1) getRangeAddress2() to get the worksheet name range
    2) Range(ra) = dprecord(1) to set the actual range value.

    Any suggestions?
    Paul
    -------------------------------------------------------------------
    Public Sub readDatapoints()
    'Macro readDataPoints
    'This macro will read in the a comma seperated value (CSV) file of
    datapoints
    'and set the data into a named range.

    Dim sFile As String
    Dim currentLine As String
    Dim delimit As String
    Dim counter As Integer
    Dim ra As String
    Dim fs As Object
    Dim ts As Object
    Dim dprecord

    Dim oldStatusBar As Boolean

    delimit = ","

    'prompt user for file
    sFile = Application.GetOpenFilename(fileFilter:="CSV Comma
    delimited (*.csv), *.csv", Title:="BCAR data")
    If Not Len(Dir(sFile)) > 0 Or sFile = "False" Then
    Exit Sub
    End If

    'We turn off calculation and screenupdating to speed up the macro.
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set ts = fs.OpenTextFile(sFile, 1, False)

    currentLine = ts.ReadLine

    oldStatusBar = Application.DisplayStatusBar

    ' Continues reading lines until there are no more.
    While (Not ts.AtEndOfStream)

    'give the user something to look at
    If counter Mod 100 = 0 Then
    Application.StatusBar = "loading BCAR data" &
    String(counter / 100, ".")
    End If

    'parse the record
    dprecord = Split(currentLine, delimit)

    'read in the records and set the referenced range value
    If UBound(dprecord) > 0 And Len(dprecord(1)) > 0 Then
    ' get the range address
    ra = getRangeAddress2("DPA_" & CStr(dprecord(0)))

    'set the value for the incoming record
    If Len(ra) > 0 Then
    Range(ra) = dprecord(1)
    End If
    End If

    currentLine = ts.ReadLine
    counter = counter + 1
    Wend

    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub

    Private Function getRangeAddress2(rname As String) As String
    Dim c As Range
    Dim returnStr As String

    '''''''''''''''''''''''''''''''''''
    ' find the datapoint address name
    ''''''''''''''''''''''''''''''''''
    'Dim lookuprange As Range
    'Set lookuprange =
    Worksheets("DPA_Control").Range("DPAControl_Range")
    'returnStr = Application.WorksheetFunction.VLookup(rname,
    lookuprange, 2, False)

    Set c = Worksheets("DPA Control").Cells.Find(What:=rname,
    LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns,
    SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)



    'if found, build and return address
    If Not c Is Nothing Then
    returnStr = c.Offset(0, 1).Value
    returnStr = "='" & Replace(returnStr, "'", "''") & "'!" & rname
    End If

    Set c = Nothing

    getRangeAddress2 = returnStr


    End Function


  2. #2
    Tom Ogilvy
    Guest

    RE: poor macro performance

    open the file in Excel,
    put down a Vlookup formula to get the address

    Pick it all up in an array

    Do the writing, or if it will be a contiguous destination, line up you data
    and put it all down at once.

    When I say put down a vlookup formula, I mean something like

    Range("C1:C34000").Formula = "=Vlookup(A1,DPA_ControlRange,2,False)"

    Do as much as you can all at once (as above and below)

    v = Range("A1:C34000).Value

    --
    Regards,
    Tom Ogilvy


    "[email protected]" wrote:

    > I have a macro to read in the a comma seperated value (CSV) file and
    > set the data into a named range. The file format is
    > <range_name>,<numeric value>. The problem is that the macro takes about
    > 8 minutes to read 34000 records and seems to gradually slow down.
    > This would lead me to believe I have a memory leak of some kind. Most
    > of the time is split evenly between two functions
    > 1) getRangeAddress2() to get the worksheet name range
    > 2) Range(ra) = dprecord(1) to set the actual range value.
    >
    > Any suggestions?
    > Paul
    > -------------------------------------------------------------------
    > Public Sub readDatapoints()
    > 'Macro readDataPoints
    > 'This macro will read in the a comma seperated value (CSV) file of
    > datapoints
    > 'and set the data into a named range.
    >
    > Dim sFile As String
    > Dim currentLine As String
    > Dim delimit As String
    > Dim counter As Integer
    > Dim ra As String
    > Dim fs As Object
    > Dim ts As Object
    > Dim dprecord
    >
    > Dim oldStatusBar As Boolean
    >
    > delimit = ","
    >
    > 'prompt user for file
    > sFile = Application.GetOpenFilename(fileFilter:="CSV Comma
    > delimited (*.csv), *.csv", Title:="BCAR data")
    > If Not Len(Dir(sFile)) > 0 Or sFile = "False" Then
    > Exit Sub
    > End If
    >
    > 'We turn off calculation and screenupdating to speed up the macro.
    > Application.Calculation = xlCalculationManual
    > Application.ScreenUpdating = False
    >
    > Set fs = CreateObject("Scripting.FileSystemObject")
    > Set ts = fs.OpenTextFile(sFile, 1, False)
    >
    > currentLine = ts.ReadLine
    >
    > oldStatusBar = Application.DisplayStatusBar
    >
    > ' Continues reading lines until there are no more.
    > While (Not ts.AtEndOfStream)
    >
    > 'give the user something to look at
    > If counter Mod 100 = 0 Then
    > Application.StatusBar = "loading BCAR data" &
    > String(counter / 100, ".")
    > End If
    >
    > 'parse the record
    > dprecord = Split(currentLine, delimit)
    >
    > 'read in the records and set the referenced range value
    > If UBound(dprecord) > 0 And Len(dprecord(1)) > 0 Then
    > ' get the range address
    > ra = getRangeAddress2("DPA_" & CStr(dprecord(0)))
    >
    > 'set the value for the incoming record
    > If Len(ra) > 0 Then
    > Range(ra) = dprecord(1)
    > End If
    > End If
    >
    > currentLine = ts.ReadLine
    > counter = counter + 1
    > Wend
    >
    > Application.StatusBar = False
    > Application.DisplayStatusBar = oldStatusBar
    >
    > Application.Calculation = xlCalculationAutomatic
    > Application.ScreenUpdating = True
    > End Sub
    >
    > Private Function getRangeAddress2(rname As String) As String
    > Dim c As Range
    > Dim returnStr As String
    >
    > '''''''''''''''''''''''''''''''''''
    > ' find the datapoint address name
    > ''''''''''''''''''''''''''''''''''
    > 'Dim lookuprange As Range
    > 'Set lookuprange =
    > Worksheets("DPA_Control").Range("DPAControl_Range")
    > 'returnStr = Application.WorksheetFunction.VLookup(rname,
    > lookuprange, 2, False)
    >
    > Set c = Worksheets("DPA Control").Cells.Find(What:=rname,
    > LookIn:=xlValues, _
    > LookAt:=xlWhole, SearchOrder:=xlByColumns,
    > SearchDirection:= _
    > xlNext, MatchCase:=False, SearchFormat:=False)
    >
    >
    >
    > 'if found, build and return address
    > If Not c Is Nothing Then
    > returnStr = c.Offset(0, 1).Value
    > returnStr = "='" & Replace(returnStr, "'", "''") & "'!" & rname
    > End If
    >
    > Set c = Nothing
    >
    > getRangeAddress2 = returnStr
    >
    >
    > End Function
    >
    >


  3. #3
    Kim Greenlee
    Guest

    RE: poor macro performance

    Just something to test for...I found that the copy/paste techniques that use
    the clipboard can generate a lot of page faults which will slow down Excel.
    To determine if you are experiencing the page fault problem:

    1. Open Task Manager
    2. If you don't see the Page Faults column, then from the File menu select
    View->Select Columns... and check the box next to Page Faults

    As Excel is loading the data, watch the Page Faults value associated with
    that instance of Excel. If page faults are the problem you will see the page
    faults growing and the program slowing.

    Just something else to look at when considering performance issues in Excel.
    I'm sure that Tom's solution will fix your problem because it handles the
    copy as one block.

    Kim

    --
    digipede - Many legs make light work.
    Grid computing for the real world.
    http://www.digipede.net
    http://krgreenlee.blogspot.net


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1