Excel VBA WorksheetFunction.SUM Returning Zero or Errors RRS feed

  • Question

  • I have posted this on other sites, so apologies if you have seen this before:

    I have a question related to Excel.WorksheetFunction.Sum. I clearly do not know how to use this function as I keep getting Zero for a result or throwing Errors.  (OR, I clearly do not understand Excel VBA Ranges)

    I simply cannot get this to work and I have been working it for three days (literally) and have performed almost a hundred distinct tests. Although I understand Access VBA well, I am self-taught and I think I am missing some key concepts and that is killing me in Excel.

    My code below is from testing/debugging that I have done and the documentation explains the results for each distinct version. The sub calls the function (which is where the problems are).

    A few points:

    1. I CAN get a SUM on the Worksheet when I am using Excel manually.

      1a. The numbers in the required Range are not always contiguous, but if I shrink the range down to just contiguous numbers - it still does NOT work.

    2. I am writing this in an ACCESS Module as this is part of an ACCESS App (trying to automate data import from a Spreadsheet).

    3. It has been implied that my prior work was not "fully qualified", so I have built this using a With Bloc. However, it is likely that I am NOT doing this correctly.

    Any guidance would be most appreciated - especially, if you could mercifully explain what concepts I am missing here.

    Public Function fnImportFileProcessFilePrep2(intClientId As Integer, intEventId As Long, strExcelFileName As String, _
    strActiveSheet As String, strQASumColumn As String)
    On Error GoTo HandleError
    Dim intLastCol As Long
    Dim intLastRow As Long
    Dim intNextCol As Long
    Dim intRecordCount As Long
    Dim varSumExcelColumns As Variant
    Dim strSUMRange As String
    Dim strAddColumnLabel As String
    Dim dblSum As Double
    Dim rgUseRange As Range
    Dim rgSUMRange As Range
    Dim strFileName As String
    Dim oXLApp As Excel.Application       'Declare the object variables
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    Set oXLApp = New Excel.Application      'Create a new instance of Excel
    Set oXLBook = oXLApp.Workbooks.Open(strExcelFileName) 'Open the existing workbook
    Set oXLSheet = oXLBook.Worksheets(strActiveSheet)  'Work with the input worksheet
      oXLSheet.Activate                   'Activate the Worksheet
      oXLApp.Visible = True               'Show it to the user
      oXLApp.UserControl = True
        With oXLSheet
            ' Replace ALL "(null)" cells - THIS WORKS!!!
                .Cells.Replace What:="(null)", _
                Replacement:="", _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
            'BOTH LastRow and LastCol WORK
            'Get Last Row & Record Count
                intLastRow = oXLSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row  'This Works
                intRecordCount = intLastRow - 1
            'Get Last Column
                intLastCol = oXLSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 'This Works
                intNextCol = intLastCol + 1
        'Get SUM of Column strQASumColumn for use in QA
            'NONE of the following work.  Note that if I use Select it's for testing so that I can look at Open Excel Sheet and see the Select Range is correct
                strSUMRange = strQASumColumn & "2:" & strQASumColumn & intLastRow '  "M2:M2934"
                Set rgSUMRange = .Range(strSUMRange)
                varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange) 'Works BUT IS ZERO??
                dblSum = Excel.WorksheetFunction.Sum(rgSUMRange) 'Works but ZERO?
                varSumExcelColumns = Excel.WorksheetFunction.Sum(oXLSheet.Range(strSUMRange))  'Works but Zero
            'Try to use Cells
                 Set rgSUMRange = .Range(.Cells(2, "M"), .Cells(intLastRow, "M"))
                 varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange) 'Works but Zero SUM
                 Set rgSUMRange = .Range(.Cells(2, intNextCol), .Cells(intLastRow, intNextCol))
                 varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange) 'Works but Zero
                 'Even Hard-coding the numbers does NOT work
                 Set rgSUMRange = .Range(.Cells(2, 13), .Cells(2934, 13)) ' Returns Zero Again
                 'rgSUMRange.Select  ' Does show the correct Range
                 varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange)
                 'Still Zero if I use a smaller range that has contiguous numbers
                 Set rgSUMRange = .Range(.Cells(3, 13), .Cells(7, 13)) ' Returns Zero Again
                 varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange)
            'All these approaches ERROR
                'varSumExcelColumns = Excel.WorksheetFunction.Sum(.Range(rgSUMRange)) 'Method Range of Object Worksheet Failed
                'varSumExcelColumns = Excel.oXLSheet.WorksheetFunction.Sum(.Range(rgSUMRange)) 'Won't compile
                ' varSumExcelColumns = Excel.WorksheetFunction.Sum("M2:M2934")  'Unable to get the SUM Property of the WorksheetFunction Class
                'varSumExcelColumns = Excel.WorksheetFunction.Sum(Range("M2:M2934")) 'Application defined or object defined error
                'dblSum = Excel.WorksheetFunction.Sum("M2:M100") 'ERROR:  Unable to get the SUM Property of the Worksheet function Class
                'dblSum = Excel.WorksheetFunction.Sum(Range("M2:M100")) 'Application defined or --- Error
                'varSumExcelColumns = Excel.WorksheetFunction.Sum(Worksheets(strActiveSheet).Range("M2", "M7")) 'ERROR Application Defined or Object Defined Error
          'Go to EMPTY Range next to the Last Column
                 varSumExcelColumns = Excel.WorksheetFunction.Sum(.Range(.Cells(2, intNextCol), .Cells(intLastRow, intNextCol)))  ' Works for SUM but is wrong Range
            'Since the above kinda worked, Try setting variables to a Range WITH Number data - Does NOT Work
                 intNextCol = 13
                 intLastRow = 7
                 varSumExcelColumns = Excel.WorksheetFunction.Sum(.Range(.Cells(2, intNextCol), .Cells(intLastRow, intNextCol)))
                 msgbox "SUM:  " & varSumExcelColumns
            'Test to see if I am still on the Correct Sheet - This WORKS
                 Dim dblCellValue As Double
                 dblCellValue = oXLSheet.Cells(2, 13).Value  'Works
        End With
        fnImportFileProcessFilePrep2 = varSumExcelColumns
        oXLBook.Close SaveChanges:=False  'SaveChanges:=True    'Save (and disconnect from) the Workbook
        oXLApp.Quit                         'Close (and disconnect from) Excel
        Set oXLSheet = Nothing               'Disconnect from all Excel objects (let the user take over)
        Set oXLBook = Nothing
        Set oXLApp = Nothing
    Exit Function
        msgbox "Error During fnImportFileProcessFilePrep2: " & Err.Description
        Resume Next
    End Function
    Private Sub TestFilePrep()
    Dim strFileNameAndPath As String
    Dim strUseWorksheet As String
    Dim intSUMColumn As Integer
    Dim strSUMColumn As String
    Dim strAddColumnLabel As String
    Dim varAddColumnFixedValue As Variant
    Dim dblSUMFromFunction As Double
    strFileNameAndPath = "C:\Users\xxxxxxxWITH NULLS2.xlsx"
    strUseWorksheet = "Sheet1"
    intSUMColumn = 13
    strSUMColumn = "M"
    strAddColumnLabel = "SourceFile"
    varAddColumnFixedValue = 77
    dblSUMFromFunction = fnImportFileProcessFilePrep2(10, -3, strFileNameAndPath, _
    strUseWorksheet, strSUMColumn)
    End Sub

    Saturday, March 18, 2017 5:03 PM


  • Try replacing Excel.WorksheetFunction with oXLApp.WorksheetFunction:

                dblSum = oXLApp.WorksheetFunction.Sum(rgSUMRange)

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by DGP13 Saturday, March 18, 2017 5:44 PM
    Saturday, March 18, 2017 5:15 PM

All replies

  • Try replacing Excel.WorksheetFunction with oXLApp.WorksheetFunction:

                dblSum = oXLApp.WorksheetFunction.Sum(rgSUMRange)

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by DGP13 Saturday, March 18, 2017 5:44 PM
    Saturday, March 18, 2017 5:15 PM
  • Re:  automating excel

    In addition to the advice from Hans...

    You cannot activate an invisible sheet.
    UserControl is a Property not a Method.
    There is more I think.

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Thursday, March 23, 2017 1:11 PM
    Saturday, March 18, 2017 5:37 PM
  • Thanks Hans,

    It looks like that was the problem.  I appreciate the reply - very helpful. 


    Saturday, March 18, 2017 5:44 PM
  • @Jim: the declaration should be

    Dim rgSumRange As Excel.Range

    Excel is the code library that provides object types such as Workbook, Worksheet and Range.

    The object instances oXLApp, oXLBook, oXLSheet etc. should be used when assigning a value to a variable such as rgSumRange

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, March 18, 2017 6:01 PM
  • Excel is a Type Library.

    Range is one of the types in this library.

    oXLApp is an instance of an Excel.Application object. It is not a type library.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, March 18, 2017 6:54 PM
  • Hans,
     Noted and thanks.

    JIm Cone

    Saturday, March 18, 2017 7:09 PM