none
Can't get WorksheetFunction.Average to work. RRS feed

  • Question

  • Hi all,

    I am trying to compute the average of a range of cells and assign it to a double variable.

    Partial code:

    Dim objAppExcel As Excel.Application
    Dim objBook As Excel._Workbook = Nothing
    Dim objBooks As Excel.Workbooks = Nothing
    Dim objSheets As Excel.Sheets = Nothing
    Dim objSheet As Excel._Worksheet = Nothing
    
    objAppExcel = New Excel.Application()
    objAppExcel.Visible = False
    objBooks = objAppExcel.Workbooks
    objBook = objAppExcel.Workbooks.Open(CurrentPath & "\myfile.xls")
    objSheets = objBook.Worksheets
    objSheet = objSheets(1)
    
    Dim StartupAverage As Double = Application.WorksheetFunction.Average("B5:E5")
    

    This returns and error, "Reference to a non-shared member requires an object reference."

    Can anyone tell me the correct syntax to get the average of the range of cells?

    Thanks,

    Monday, July 16, 2018 5:13 PM

Answers

  • You need to reference the worksheet that contains that range:

    Dim StartupAverage As Double = Application.WorksheetFunction.Average(objSheet.Range("B5", "E5"))


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by sesheldon Tuesday, July 17, 2018 3:40 PM
    Monday, July 16, 2018 5:21 PM
    Moderator

All replies

  • You need to reference the worksheet that contains that range:

    Dim StartupAverage As Double = Application.WorksheetFunction.Average(objSheet.Range("B5", "E5"))


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by sesheldon Tuesday, July 17, 2018 3:40 PM
    Monday, July 16, 2018 5:21 PM
    Moderator
  • Thanks, Reed, that was the answer.  I ended up using this:

    Dim StartupAverage As Double = objAppExcel.WorksheetFunction.Average(objSheet.Range("B5:E5"))
    

    Steve

    Monday, July 16, 2018 5:47 PM
  • OK, how would I reference the range numerically?

    What I am actually trying to achieve is take the average of some cells identified using the Find method, which returns numeric values for columns.

    So instead of a range ("B5, "E5"), what I want is a range ( (2,2), (2,5) ).

    So I tried this:

    Dim StartupAverage As Double = objAppExcel.WorksheetFunction.Average(objSheet.Range(objSheet.Cells(2, 2), objSheet.Cells(2, 5)))

    But it fails with an error.

    Any ideas?

    Monday, July 16, 2018 6:17 PM
  • Here's information on the algorithm to perform the conversion:

    https://support.microsoft.com/en-us/help/833402/how-to-convert-excel-column-numbers-into-alphabetical-characters


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Monday, July 16, 2018 6:31 PM
    Moderator
  • I solved it thusly:

    (code snippet, not complete)

    Dim FirstColumnLetter As String = Nothing
    Dim FoundFirstColumn As Integer = 0 Dim LastColumnLetter As String = Nothing
    Dim FoundNextColumn As Integer = 0 Dim GCell As Range GCell = objSheet.Cells.Find(strHeaderText) FoundFirstColumn = GCell.Column FirstColumnLetter = Split(GCell.Address, "$")(1) While FoundNextColumn <> FoundFirstColumn GCell = objSheet.Cells.FindNext(GCell) FoundNextColumn = GCell.Column End While GCell = objSheet.Cells.FindPrevious(GCell) FoundNextColumn = GCell.Column LastColumnLetter = Split(GCell.Address, "$")(1) 'Display the first and last header cell locations. Console.WriteLine("The first header column letter is " + FirstColumnLetter) Console.WriteLine("The last header column letter is " + LastColumnLetter) Dim StartupRange As String = FirstColumnLetter + "5:" + LastColumnLetter + "5" Console.WriteLine("Startup is: " + StartupRange) Dim StartupAverage As Double = objAppExcel.WorksheetFunction.Average(objSheet.Range(StartupRange)) Console.WriteLine("Average Startup Time: {0}", StartupAverage)

    Note that in my case I know precisely the rows where certain data is always stored, so that is hard coded (5 in this case).  What I needed to dynamically identify was the number and location of the rows of data.  Each row corresponds to another set of time data, and the number of rows can vary depending on how many datasets of time are chosen (elsewhere) to be recorded.

    The entire program basically identifies all the columns of data, inserts a new column, and then populates it with the averages from the identified columns.

    Thanks for the help, Reed!


    Monday, July 16, 2018 7:15 PM