• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

Office 2010 Macro Upgrade

Hi Experts,

Recently my office server has been upgraded with office 2010 and I need to upgrade the excel files and macros from excel 2003 to excel 2010.

Now I have only changed certain functions of 2003 to Long Data type from Integer Data Type. And It results very slow execution for the functions.

Reports are now taking 15 minutes compare to 5 minutes execution. What is happening there? How to resolve this?

Also, I need to upgrade the below function for Office 2010...please help.

Public Function LoadConnection(sServer As String, sDatabase As String, sLogin As String, sPassword As String) As Boolean
On Error GoTo ErrCheck
sConn = "ODBC;DRIVER=SQL Server;SERVER=" & sServer & ";UID=" & sLogin & ";PWD=" & sPassword & ";APP=Microsoft Office 2003;DATABASE=" & sDatabase & ";"
LoadConnection = True
Exit Function
ErrCheck:
MsgBox Err.Description
Err.Clear
End Function

Public Function LoadXMLFromString(sXML As String) As Boolean
Dim oNodes As Object, oNode As Object

On Error GoTo ErrCheck
Set oXDoc = CreateObject("MSXML2.DOMDocument")
oXDoc.loadXML sXML

Set oNodes = oXDoc.selectNodes("//Parameters")(0).childNodes
Set colParams = New Collection
For Each oNode In oNodes
     colParams.Add oNode.Text, oNode.Attributes(0).Text
Next
Set oXDoc = Nothing
LoadXMLFromString = True
Exit Function
ErrCheck:
MsgBox Err.Description
Err.Clear
End Function

Public Sub GetDataForQuery2007(sRangeName As String, sSPCommand As String, sODBCConnectionName As String, bPivotTableYesNo As Boolean)
sRangeName = ReturnRangeWithValidSheet(sRangeName)
On Error GoTo ErrCheck
    With ActiveWorkbook.Connections(sODBCConnectionName).ODBCConnection
        .BackgroundQuery = False
        .Connection = sConn
        .CommandText = Array( _
        sSPCommand)
        .CommandType = xlCmdSql
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    ActiveWorkbook.Connections(sODBCConnectionName).Refresh
    If Not bPivotTableYesNo Then
        If Application.Range(sRangeName).Worksheet.AutoFilterMode Then
            Application.Range(sRangeName).AutoFilter
        End If
       ' Application.Range(sRangeName).AutoFilter
    End If
Exit Sub
ErrCheck:
MsgBox "Error in retrieving data in [" & sRangeName & "]" & vbCrLf & "Err: " & Err.Description, vbCritical
Err.Clear
End Sub


Public Sub GetDataForPivotTableRange(sRangeName As String, sSPCommand As String, sPivotTableName As String)
sRangeName = ReturnRangeWithValidSheet(sRangeName)
On Error GoTo ErrCheck
Application.Range(sRangeName).Worksheet.Select
Application.Range(sRangeName).Select
With Application.Selection
    .Worksheet.PivotTableWizard SourceType:=xlExternal, SourceData:=Array(sSPCommand _
        ), BackgroundQuery:=False, Connection:= _
        sConn
    .Worksheet.PivotTables(sPivotTableName).PivotCache.OptimizeCache = False
    .Worksheet.PivotTables(sPivotTableName).PivotCache.BackgroundQuery = True
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveWorkbook.ShowPivotTableFieldList = False
End With
Exit Sub
ErrCheck:
MsgBox "Error in retrieving data in [" & sRangeName & "]" & vbCrLf & "Err: " & Err.Description, vbCritical
Err.Clear
End Sub

Public Sub RefreshDataForLocalPivot(sRangeName As String, sPivotTableName As String)
sRangeName = ReturnRangeWithValidSheet(sRangeName)
On Error GoTo ErrCheck
Application.Range(sRangeName).Worksheet.Select
Application.Range(sRangeName).Select
Application.Selection.Worksheet.PivotTables(sPivotTableName).PivotCache.Refresh
Exit Sub
ErrCheck:
MsgBox "Error in retrieving data in [" & sRangeName & "]" & vbCrLf & "Err: " & Err.Description, vbCritical
Err.Clear
End Sub
Private Function PreserveData(oRange As Range, bRecord As Boolean)
Dim iCtr As Long

If bRecord Then
    Set oCol = New Collection
    For iCtr = 1 To oRange.Count
        oCol.Add oRange(iCtr).Formula
    Next
Else
    For iCtr = 1 To oRange.Count
        oRange(iCtr).Formula = oCol(iCtr)
    Next
End If
End Function


Private Function ReturnRangeWithValidSheet(sRangeName As String) As String
Dim iTemp As Long, sTemp As String
iTemp = InStr(1, sRangeName, "!")
If iTemp > 0 Then
    sTemp = Mid(sRangeName, 1, iTemp - 1)
    If InStr(1, sTemp, " ") Or InStr(1, sTemp, "-") Then
        If Left(sTemp, 1) = "'" And Right(sTemp, 1) = "'" Then
            ReturnRangeWithValidSheet = sRangeName
        Else
            ReturnRangeWithValidSheet = "'" & sTemp & "'!" & Mid(sRangeName, iTemp + 1)
        End If
    Else
        ReturnRangeWithValidSheet = sRangeName
    End If
Else
    ReturnRangeWithValidSheet = sRangeName
End If
End Function

Private Sub SelectFirstSheet()
Dim iCtr As Long
For iCtr = 1 To Application.Sheets.Count
    If Application.Sheets(iCtr).Visible Then
        Application.Sheets(iCtr).Select
        Exit For
    End If
Next
End Sub

Public Function CreateTotals(oRange As Range, Optional sExceptionColumnIndexes As String = "", Optional iTotalInRow As Integer = 0)

Dim iCtr As Long, m_iTotalInRow As Long, oTotRange As Range

If m_iTotalInRow = 0 Then m_iTotalInRow = oRange.Row + oRange.Rows.Count
If Len(sExceptionColumnIndexes) > 0 Then sExceptionColumnIndexes = "," & sExceptionColumnIndexes & ","

With oRange
For iCtr = 1 To .Columns.Count
    If InStr(1, sExceptionColumnIndexes, "," & iCtr & ",") = 0 Then
        Set oTotRange = .Application.Range(oRange.Application.ConvertFormula("R" & m_iTotalInRow & "C" & iCtr, xlR1C1, xlA1))
        .Application.Range(.Application.ConvertFormula("R" & m_iTotalInRow & "C" & iCtr, xlR1C1, xlA1)).Formula = _
            "=SUM(" & oRange.Columns(iCtr).Address & ")"
        oTotRange.Font.Bold = True
       Else
        Set oTotRange = .Application.Range(oRange.Application.ConvertFormula("R" & m_iTotalInRow & "C" & iCtr, xlR1C1, xlA1))
        .Application.Range(.Application.ConvertFormula("R" & m_iTotalInRow & "C" & iCtr, xlR1C1, xlA1)).Formula = _
            ""
    End If
Next
End With

End Function

Open in new window

0
suvmitra
Asked:
suvmitra
  • 3
  • 3
2 Solutions
 
Rory ArchibaldCommented:
If you are switching from 2003 to 2010, then you have clearly done a lot more than change some variables from Integer to Long, since a lot of that code would not work in 2003 at all. (incidentally, VBA would have converted your integers to Long under the hood anyway, so I doubt that is your issue).
0
 
suvmitraAuthor Commented:
Actually I changed certain things manually .. not converted the files ...

1. Opened the 2003 file in compatibility mode and saved it as Excel Macro Enabled Workbook (.xlsm)
2. Later I checked the code section and only changed the integer(s) to Long Data Type.

Only doing the above two things made the reporting performance noticeably very poor. The same report is taking more than double time.

Please help and let me know for further information on this. Thanks.
0
 
Rory ArchibaldCommented:
If you are passing entire rows or columns to your functions, you now have a LOT more rows and columns to iterate so that would certainly cause a slowdown.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
suvmitraAuthor Commented:
Very True ... using integer giving me an overflow error .. but I am checking this now...thank you.
0
 
Rory ArchibaldCommented:
FYI, you shouldn't ever use Integer for row counters anyway - even in XL2000 there were more rows than an integer could cope with!
0
 
Corey ScheichDeveloperCommented:
I would start by turning screen updating off.  You will be amazed at how much time can be wasted in a macro because of selections and edits causing screen updates too frequently.

Application.ScreenUpdating = False

Beyond that I would add

Debug.Print(format(now,"HH:mm:ss.fff" & " <Some Unique code location identifier>")

at strategic locations in the code to see where you are loosing time.

Also concider using Intersect(sRangeName, sRangeName.WorkSheet.UsedRange) to limit your code to the edited portion of the spreadsheet only

0
 
suvmitraAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now