Validate named range names

[Deutz]

Hi and thanks in advance,

I have a list of names and corresponding range addresses in a sheet that I would like to create named ranges from in VBA. I have that part working ok but I would like to validate each name before I create the named range, in case the name has reserved characters, references a cell address, too long etc which will cause my 'add name' code to crash.

Is there a simple way I could do this in VBA?

Kind regards
Deutz

 

[Akuini]

Try using this function:

Function valid_name(tx As String) As Boolean
     On Error Resume Next
        ThisWorkbook.Sheets(1).Range("A1").Name = tx
        If Err.Number = 0 Then valid_name = True
     On Error GoTo 0
End Function

For example, you want to create a named range "abc_1", you use it like this:

If valid_name("abc_1") Then
    'create the named range here
End If

 

[Deutz]

Try using this function:

Function valid_name(tx As String) As Boolean
     On Error Resume Next
        ThisWorkbook.Sheets(1).Range("A1").Name = tx
        If Err.Number = 0 Then valid_name = True
     On Error GoTo 0
End Function

For example, you want to create a named range "abc_1", you use it like this:

If valid_name("abc_1") Then
    'create the named range here
End If

Thankyou Akuini, that works exactly as required. Much appreciated

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Akuini]

@Deutz
I forgot something.
The 'valid_name' function in post #2 actually creates a named range in this part:

        ThisWorkbook.Sheets(1).Range("A1").Name = tx

it would be better if it is deleted once it is created, because there's still a chance that it won't be deleted when you create a named range with the same name in this part:

If valid_name("abc_1") Then
    'create the named range here
End If

the reason is that you can have 2 named ranges with the same name if they are in different scope.
So use this one instead:

Function valid_name(tx As String) As Boolean
     On Error Resume Next
        ThisWorkbook.Sheets(1).Range("A1").Name = tx
        If Err.Number = 0 Then
            valid_name = True
            ThisWorkbook.Names(tx).Delete
        End If
     On Error GoTo 0
End Function

 

[jkpieterse]

Our (free!) Name Manager has a built-in way to:
- Generate a list of all range names
- Use the same list (after editing or adding names) to update the document with the edited and added names
It will report any names that are "illegal"

 

[Deutz]

@Deutz
I forgot something.
The 'valid_name' function in post #2 actually creates a named range in this part:

        ThisWorkbook.Sheets(1).Range("A1").Name = tx

it would be better if it is deleted once it is created, because there's still a chance that it won't be deleted when you create a named range with the same name in this part:

If valid_name("abc_1") Then
    'create the named range here
End If

the reason is that you can have 2 named ranges with the same name if they are in different scope.
So use this one instead:

Function valid_name(tx As String) As Boolean
     On Error Resume Next
        ThisWorkbook.Sheets(1).Range("A1").Name = tx
        If Err.Number = 0 Then
            valid_name = True
            ThisWorkbook.Names(tx).Delete
        End If
     On Error GoTo 0
End Function

Thanks again Akuini, works well