Cell validation problem
 

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

Cell validation problem
 

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
"Fredrik Wahlgren" schrieb im
Newsbeitrag ...
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

Cell validation problem
 

"Frank Kabel" wrote in message
...
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
"Fredrik Wahlgren" schrieb im
Newsbeitrag ...

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

Cell validation problem
 

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


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.


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

Cell validation problem
 

"Dick Kusleika" wrote in message
...
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

Cell validation problem
 

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

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

Cell validation problem
 

Hi Dick

Thank you very much.

/ Fredrik

"Dick Kusleika" wrote in message
...


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

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