Check if reference to cell is Valid-CodePudding

I am importing a csv file which has cell references to a worksheet. The cell reference can be wrong, because the user once made an error typing the cell reference (for example: instead of "AM1" the user wrote "AMQ") or something different went wrong.

The problem is, that I have more than 1000 cell references which can change dynamically and there can be any error imaginable, as the user can write anything into it.

I am trying to write a function which checks if the reference is valide, but I can't find a solution for checking if the cell reference exists in excel.

For example, I have this code but it always gives a run time error back, because the Range(rng).Row function doesn't work if rng is not a valide input. But how do I work around it?

This as exptected always works:

Sub Test()
    rng = "A1"
    flag = rangeExists(rng)
    debug.print flag
End Sub

This doesn't work:

Sub Test()
    rng = "AQ"
    flag = rangeExists(rng)
    debug.print flag
End Sub

The function:

Function rangeExists(ByVal rng As String) As Boolean
    
    Dim row_int As Integer
    
    row_int = Range(rng).Row
    On Error Resume Next
    
    If Err.Number <> 0 Then
        rangeExists = False
    Else
        rangeExists = True
    End If

    On Error GoTo -1

End Function

I am trying to find a solution with the Error Handling in VBA, but I am not able to do so. Do you have any idea how to solve this?

CodePudding user response：

This will simply return True if it's a valid range, and False if it's not.

Function IsRangeValid(ByVal rng As String) As Boolean
    Dim r As Range
    On Error Resume Next
    Set r = Range(rng)
    On Error Goto 0
    IsRangeValid = (Not r Is Nothing) ' parenthesis optional
End Function