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