Answered by:
Excel VBA WorksheetFunction.SUM Returning Zero or Errors
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 selftaught 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:

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.

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

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 Hardcoding 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
Saturday, March 18, 2017 5:03 PM 
Answers

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 addins & 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.
DGP
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.'
Regards,
JIm ConeSaturday, March 18, 2017 7:09 PM