Excel VBA SUM returning zero or error

Question

I have tried to simplify a prior post by focusing on just one 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.

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, _
            MatchCase:=False


        '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)
            'rgSUMRange.Select
            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"))
             rgSUMRange.Select
             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
             rgSUMRange.Select
             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
             'THE ABOVE ACTUALLY WORKS, BUT ONLY IF I GO TO OPEN SPREADSHEET AND MANUALLY ENTER NUMBERS INTO THE RANGE AREA ?????????

        '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




Exit_Label:
    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

HandleError:


    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

Answer 1

use:

varSumExcelColumns = oXLApp.WorksheetFunction.Sum(rgSUMRange)

since your Excel application is "represented" in oXLApp object

Answer 2

I think the problem might be (at least in part) the way you declare your range variables. I've not worked with VBA in Access, but I've used VBA in Excel to create and manipulate Word docs, and I ran into a similar problem.

When you declare a range variable, you need to specify that it's an Excel range variable (if that's how it's used). So when you

Dim rgUseRange As Range
Dim rgSUMRange As Range

It should instead be

Dim rgUseRange as Excel.Range
Dim rgSUMRange as Excel.Range

This is necessary because the Access object model also has a Range object, but it's different from the Access Range object (and the Word Range object).

Assume this to be true of any object from anywhere but the "native" object model that you're working with. Although, it's fine to specify that object model as well.

I sure hope this helps.

Edit: Apparently there's no Range object in Access. I'd still try this to see if it helps.