How to open one workbook find a max value in one worksheet and return it from a function

[Nicole H]

Full disclaimer I have only been using VBA for very small projects and have never attempted working with multiple workbooks.

I have a user form in 365 that enters data per the form's request. I am using 3 files, an entry file, a database file, and a read file. Between the database file and entry file I want to grab the largest number in the range from B3 to B2000 and return if from a function into an integer variable. I have based my code off of Irrobo to make sure it wasn't something wrong with my files and this code works:

Sub test()
Dim AR()
Dim AM()
Dim LC      As Long
Dim fd      As FileDialog
Dim wb      As Workbook

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .AllowMultiSelect = True
    .Show
    
    For LC = 1 To .SelectedItems.Count
        ReDim Preserve AR(1 To LC)
        AR(LC) = .SelectedItems(LC)
    Next LC
End With

For i = 1 To UBound(AR())
    Set wb = Application.Workbooks.Open(AR(i))
    ReDim Preserve AM(1 To i)
    AM(i) = Application.WorksheetFunction.Max(wb.Sheets("Sheet1").Range("A20:A500"))
    wb.Close
    Set wb = Nothing
Next i

Range("A1:A" & UBound(AM)) = Application.WorksheetFunction.Transpose(AM())

End Sub

But I want to do it for just one specific file and instead of put the value in a sheet, just return the value from a function to an integer variable. I didn't want to base it off an array. I was just trying to get it to work for this case. When I use this, I get error 9: script out of range. Please show example code or solution. I'm not sure what's actually out of range/what I'm doing wrong.

Private Function Maxnum()

    Dim AM()
    Dim wb As Workbook
    
    Set wb = Application.Workbooks.Open("C:\Database.xlsb")
    AM(1) = Application.WorksheetFunction.Max(wb.Sheets("DatabaseSheet").Range("B3:B2000"))

    wb.Close
    Set wb = Nothing

    Maxnum = AM(1)

End Function

 

[DanteAmor]

Try this.

Run Macro1.
Check the folder name, workbook name, and sheet name:

Sub Macro1()
  Dim MaxValue As Double
  Dim sPath As String, sFile As String, sh As String, sRng As String
 
  sPath = "C:\"       'fit path name
  sFile = "Database.xlsb"     'fit file name
  sh = "DatabaseSheet"        'fit sheet name
  sRng = "B3:B2000"           'fit range
 
  MaxValue = getMaxValue(sPath, sFile, sh, sRng)
End Sub

Function getMaxValue(sPath As String, sFile As String, sh As String, sRng As String)
  Dim wb As Workbook
 
  Set wb = Workbooks.Open(sPath & sFile)
  getMaxValue = Application.WorksheetFunction.Max(wb.Sheets(sh).Range(sRng))
  wb.Close False
End Function

 

[Nicole H]

Try this.

Run Macro1.
Check the folder name, workbook name, and sheet name:

Sub Macro1()
  Dim MaxValue As Double
  Dim sPath As String, sFile As String, sh As String, sRng As String
 
  sPath = "C:\"       'fit path name
  sFile = "Database.xlsb"     'fit file name
  sh = "DatabaseSheet"        'fit sheet name
  sRng = "B3:B2000"           'fit range
 
  MaxValue = getMaxValue(sPath, sFile, sh, sRng)
End Sub

Function getMaxValue(sPath As String, sFile As String, sh As String, sRng As String)
  Dim wb As Workbook
 
  Set wb = Workbooks.Open(sPath & sFile)
  getMaxValue = Application.WorksheetFunction.Max(wb.Sheets(sh).Range(sRng))
  wb.Close False
End Function

Thank you so much! works like a charm!!!!!!!!!!!!!!!!!!!!!!!!!!!!!