How do I test whether a string is a valid cell reference?
in [Programming]
|
From: JT on 15 Apr 2010 07:41 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
From: sali on 15 Apr 2010 08:09 "JT" <tilleyjk(a)hotmail.com> je napisao u poruci interesnoj grupi:e3c91e3f-9994-413e-9e8b-3f01eee77d00(a)30g2000yqi.googlegroups.com... >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. 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
From: JLatham on 15 Apr 2010 08:29 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 On Error GoTo 0 ' reset error trapping "JT" wrote: > 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 > . >
From: JT on 15 Apr 2010 08:44 On Apr 15, 1:29 pm, JLatham <JLat...(a)discussions.microsoft.com> wrote: > 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 > On Error GoTo 0 ' reset error trapping > > > > "JT" wrote: > > 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 > > .- Hide quoted text - > > - Show quoted text - 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
From: JLatham on 15 Apr 2010 20:43
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. "JT" wrote: > On Apr 15, 1:29 pm, JLatham <JLat...(a)discussions.microsoft.com> wrote: > > 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 > > On Error GoTo 0 ' reset error trapping > > > > > > > > "JT" wrote: > > > 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 > > > .- Hide quoted text - > > > > - Show quoted text - > > 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 > . > |