The purpose of the following Excel VBA code is to copy into an array of Variant the value of every cell contained within the used region of the spreadsheet.
Public Function GetSingletonSpreadsheetArray(spreadsheetName As String) As Variant
Dim lastRow As Long
Dim lastCol As Integer
Dim ws As Worksheet
If m_dictSpreadsheetArray Is Nothing Then
Set m_dictSpreadsheetArray = New Dictionary
End If
If m_dictSpreadsheetArray.Exists(spreadsheetName) Then
GetSingletonSpreadsheetArray = m_dictSpreadsheetArray(spreadsheetName)
Exit Function
End If
Set ws = Worksheets(spreadsheetName)
lastRow = ws.UsedRange.Rows.Count
lastCol = ws.UsedRange.Columns.Count
m_dictSpreadsheetArray.Add spreadsheetName, ws.Range(Cells(1, 1), Cells(lastRow, lastCol)).Value
Set ws = Nothing
GetSingletonSpreadsheetArray = m_dictSpreadsheetArray(spreadsheetName)
End Function
The Excel VBA debugger never returns attempting to single step through this line (from the above code):
lastCol = ws.UsedRange.Rows.Count
Placing ws in Watch Window shows a mostly valid Worksheet object. Conspicuously missing is the UsedRange value:
Why does this Worksheet object have no UsedRange value? How do I get the last used row and column from this Worksheet object? The corresponding spreadsheet definitely has data in it... about 5,000 rows using columns A through T.
