Avoid startup errors for every loop of a cell in a range?
I wrote the function below. It usually works fine. But when r is Nothing, I get an error on the line For each c In r.Cells tells me "Object variable or With block variable not set".
I don't know exactly why this is happening. I imagined that if r was Nothing, the loop simply wouldn't start.
Function CSVFromRange(r As Range) As String
Dim sTemp As String
Dim c As Range
'Append value and comma
For Each c In r.Cells
sTemp = sTemp & "," & c.value
Next c
'Remove first comma
If Len(sTemp) > 0 Then
sTemp = Right(sTemp, Len(sTemp) - 1)
End If
CSVFromRange = sTemp
End Function
Please tell me an elegant way to make this function not throw an error and also educate me if you can about why it is throwing an error.
source to share
Why is this causing an error?
because it expects a valid range to be passed. See this example
Sub Sample()
Dim r As Range
Dim ret
ret = CSVFromRange(r)
End Sub
Function CSVFromRange(r As Range) As String
CSVFromRange = r.Value
End Function
This is the shortest way to create an error in your case. The range object is passed, but it is not initialized and hence you will get an error. You need to make sure that you have passed a valid range. for example
Sub Sample()
Dim r As Range
Dim ret
Set r = ThisWorkbook.Sheets("Sheet1").Range("A1")
ret = CSVFromRange(r)
End Sub
How can we deal with this error?
You can handle it in the caller of the sub or the function itself. I'll show you both
In Sub
Sub Sample()
Dim r As Range
Dim ret
If Not r Is Nothing Then ret = CSVFromRange(r)
End Sub
Function CSVFromRange(r As Range) As String
CSVFromRange = r.Value
End Function
In function
Sub Sample()
Dim r As Range
Dim ret
ret = CSVFromRange(r)
End Sub
Function CSVFromRange(r As Range) As String
If Not r Is Nothing Then
CSVFromRange = r.Value
End If
End Function
or
Sub Sample()
Dim r As Range
Dim ret
ret = CSVFromRange(r)
End Sub
Function CSVFromRange(r As Range) As String
On Error GoTo ExitGracefully
CSVFromRange = r.Value
Exit Function
ExitGracefully:
MsgBox Err.Description '<~~ comment this if you do not want any alerts
End Function
source to share
The simplest answer is to check if there is r
Nothing
Function CSVFromRange(r As Range) As String
Dim sTemp As String
Dim c As Range
If r = Nothing Then
sTemp = vbNullString 'You can change this to a different default return value
Else
'Append value and comma
For Each c In r.Cells
sTemp = sTemp & "," & c.value
Next c
'Remove first comma
If Len(sTemp) > 0 Then
sTemp = Right(sTemp, Len(sTemp) - 1)
End If
Endif
CSVFromRange = sTemp
End Function
source to share
I am using On Error Goto, you can see the code:
Function CSVFromRange(r As Range) As String
Dim sTemp As String
Dim c As Range
On Error GoTo DisplayError
'Append value and comma
For Each c In r.Cells
sTemp = sTemp & "," & c.value
Next c
'Remove first comma
If Len(sTemp) > 0 Then
sTemp = Right(sTemp, Len(sTemp) - 1)
End If
CSVFromRange = sTemp
Exit Function
DisplayError:
... Display a messagebox with error
End Function
source to share
The above answers answered the specific question you asked. I would also recommend removing the if / end if at the bottom and using everything in the For loop instead. Exception condition will prevent double commas when there is a blank cell.
Function CSVFromRange(r As Range) As String
Dim sTemp As String
Dim c As Range
'Append value and comma
For Each c In r.Cells
If Not IsEmpty(c) And c <> r.Cells.Item(1) Then
sTemp = sTemp & "," & c
Else
sTemp = sTemp & c
End If
Next c
CSVFromRange = sTemp
End Function
source to share