When does Excel's Range object's Text property return Null
Was writing a function to determine whether a particular row in an Excel spreadsheet was empty or not. My spreadsheet has 21 columns labled. For me, if the 22nd column contains any text I wouldn't care as long as the other columns contain text. I wanted to detect if all of those 21 columns were empty. Here's my function.
Code:
Public Enum SpreadsheetColumns
EquipmentID = 0
Supplier = 1
Manufacturer = 2
ModelNumber = 3
LocationDept = 4
SerialNumber = 5
AcquisitionTerms = 6
LeaseEndDate = 7
PurchaseDate = 8
PurchasePrice = 9
Depreciation = 10
IsColorDevice = 11
PagesPrintedPerMonth = 12
AmountSupplies = 13
AmountServices = 14
CPPTotal = 15
CPPSupplies = 16
CPPService = 17
NumInvoices = 18
ApplicationData = 21
End Enum
Public Function GetColumnAlphabetFromIndex(ByVal ZeroBasedColumnIndex As SpreadsheetColumns) As String
If ZeroBasedColumnIndex < 0 Or ZeroBasedColumnIndex > 21 Then Exit Function
GetColumnAlphabetFromIndex = Choose _
(ZeroBasedColumnIndex + 1, _
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V")
End Function
Public Function IsRowEmpty(ByVal RowNumber As Long) As Boolean
Dim StrFromCell As String
Dim StrToCell As String
StrFromCell = GetColumnAlphabetFromIndex(SpreadsheetColumns.EquipmentID) & CStr(RowNumber)
StrToCell = GetColumnAlphabetFromIndex(SpreadsheetColumns.ApplicationData) & CStr(RowNumber)
If IsNull(Sheet1.Range(StrFromCell & ":" & StrToCell).Text) Then
IsRowEmpty = True
Else
IsRowEmpty = Trim(Sheet1.Range(StrFromCell & ":" & StrToCell).Text) = vbNullString
End If
End Function
Why does the line:
Code:
Sheet1.Range(StrFromCell & ":" & StrToCell).Text
return a value Null in my code when I actually have data in the row?
