How do I test whether a string is a valid cell reference?

  • Thread starter Thread starter JT
  • Start date Start date
J

JT

I would like to develop a way of testing whether a string entered by a
user can be used by excel to define a range of cells within a sheet.

For example: "A1:C6" or "A:Z" would be OK but "iljfneklj" would not.

I'd like to have ways of doing this both in VBA and also using an
excel formula.

Are there any ideas out there?

Thanks

John
 
JT said:
I would like to develop a way of testing whether a string entered by a
user can be used by excel to define a range of cells within a sheet.

For example: "A1:C6" or "A:Z" would be OK but "iljfneklj" would not.

I'd like to have ways of doing this both in VBA and also using an
excel formula.
Click to expand...

when testing expressions, i let excel to do the job, and report, if error,
so

function test1(reference as string) as boolean
dim v as range
on error resume next
set v=range(reference) 'try to use referenced range, is address valid?
if err.number>0 then
exit functio 'return false
end if
test1=true
end function
 
Rather than trying to parse the text, you could use something like this:

'near the beginning of your process
Dim testForRange As Range
dim testAddress As String

....in here you get the string and
....put it in the example testAddress variable
.... now you test it using error trapping
.... assumes the range is to be on the current active sheet
On Error Resume Next
Set testForRange = ActiveSheet.Range(testAddress)
If Err <> 0 then
'had an error, presumed invalid address string
MsgBox testAddress & " is not a valid range address."
Err.Clear ' clear the error
End If
 
Rather than trying to parse the text, you could use something like this:

'near the beginning of your process
Dim testForRange As Range
dim testAddress As String

...in here you get the string and
...put it in the example testAddress variable
... now you test it using error trapping
... assumes the range is to be on the current active sheet
On Error Resume Next
Set testForRange = ActiveSheet.Range(testAddress)
If Err <> 0 then
   'had an error, presumed invalid address string
   MsgBox testAddress & " is not a valid range address."
   Err.Clear ' clear the error
End If





- Show quoted text -
Click to expand...

Thanks both

I have now also applied this principle to achieve the same using an
excel formula:

Where C36 contains a user input cell reference:
=IF(C36="","OK",IF(ISERROR(ROWS(INDIRECT(C36))),"INVALID CELL
REFERENCE","OK"))

John
 
Glad our ideas gave you an idea. Didn't realize you wanted to do it in a
worksheet cell, so I gave up the VBA code.
 
Glad our ideas gave you an idea.  Didn't realize you wanted to do it ina
worksheet cell, so I  gave up the VBA code.









- Show quoted text -
Click to expand...

I was looking to do both, so thanks!
 
Back
Top