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

LVL 9
suvmitraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.