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

Nicole H

New Member
Joined
Jan 25, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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.

VBA Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try this.

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

VBA Code:
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

:)
 
Upvote 1
Solution
Try this.

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

VBA Code:
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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top