Cell validation problem

[Fredrik Wahlgren]

Hi

I have a function which takes a cell reference as a parameter. The function
is similar to this

Function MyRef(wb As String, ws As String, cell As String)
MyRef = Application.Evaluate("[" + wb + ".xls]" + ws + "!" + cell)
End Function

The idea behind this function is simple. Instead of entering something like
this:

=[SomeSheet.xls]Sheet1!A1

I can now do this:

=MyRef("SomeSheet","Sheet1","A1")

How can I validate the last parameter? "A1" is a valid cell reference while
"XX23" isn't.
I want to to show a custom error messsage in this case.

TIA,
Fredrik

 

[Frank Kabel]

Hi
not really sure why you're doing this (as this slows down Excel and you may
use INDIRECT instead). But maybe enclose your function in an
on error resume next
statement and check err.number afterwards

 

[Fredrik Wahlgren]

Hi
not really sure why you're doing this (as this slows down Excel and you may
use INDIRECT instead). But maybe enclose your function in an
on error resume next
statement and check err.number afterwards

--
Regards
Frank Kabel
Frankfurt, Germany
Newsbeitrag news:[email protected]...

I don't think I can use INDIRECT with external sheets. The idea is to have a
master sheet in which I get data from othe sheets generated by a program.
The sheets change every month so I want to have a way to quickly change the
reference. It doesn't matter that it slows down Excel. The names of the
generated sheets change in a predictable way.

/ Fredrik

 

[Dick Kusleika]

Fredrik

One way

Function MyRef(wb As String, ws As String, cell As String)

Dim rTest as Range

On Error Resume Next
Set rTest = ActiveSheet.Range(cell)
On Error Goto 0

If rTest Is Nothing Then
MyRef = "Invalid Cell Reference"
Else

MyRef = Application.Evaluate("[" + wb + ".xls]" + ws + "!" + cell)

End If

 

[Fredrik Wahlgren]

Fredrik

One way

Function MyRef(wb As String, ws As String, cell As String)

Dim rTest as Range

On Error Resume Next
Set rTest = ActiveSheet.Range(cell)
On Error Goto 0

If rTest Is Nothing Then
MyRef = "Invalid Cell Reference"
Else

MyRef = Application.Evaluate("[" + wb + ".xls]" + ws + "!" + cell)

End If

End Function

Yes, this seems to solve the problem. I have to admit that I thought there
was another way to do this, one that wouldn't result in a run time error.

Thank you,
Fredrik

 

[Dick Kusleika]

Yes, this seems to solve the problem. I have to admit that I thought there
was another way to do this, one that wouldn't result in a run time error.

Other than string manipulation, I can't think of a way.

Function IsValidRange(sInput As String) As Boolean

Dim sCol As String
Dim lRow As Long
Dim i As Long

For i = 1 To Len(sInput)
If IsNumeric(Mid(sInput, i, 1)) Then
If IsNumeric(Mid(sInput, i)) Then
lRow = CLng(Mid(sInput, i))
Exit For
Else
IsValidRange = False
Exit Function
End If
Else
sCol = sCol & Mid(sInput, i, 1)
End If
Next i

If lRow > ActiveSheet.Rows.Count Then
IsValidRange = False
Else
If sCol > "IV" Then
IsValidRange = False
Else
IsValidRange = True
End If
End If

End Function

 

[Fredrik Wahlgren]

Hi Dick

Thank you very much.

/ Fredrik

Other than string manipulation, I can't think of a way.

Function IsValidRange(sInput As String) As Boolean

Dim sCol As String
Dim lRow As Long
Dim i As Long

For i = 1 To Len(sInput)
If IsNumeric(Mid(sInput, i, 1)) Then
If IsNumeric(Mid(sInput, i)) Then
lRow = CLng(Mid(sInput, i))
Exit For
Else
IsValidRange = False
Exit Function
End If
Else
sCol = sCol & Mid(sInput, i, 1)
End If
Next i

If lRow > ActiveSheet.Rows.Count Then
IsValidRange = False
Else
If sCol > "IV" Then
IsValidRange = False
Else
IsValidRange = True
End If
End If

End Function

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com