What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.


--
John C

It is quite simple.

The value is returned as the function name

Function NullCell()

'some code
NullCell = somevalue
End Function

If you want to pass a value into the function you just declare an argument

Function NullCell(ByRef rng As Range)

'do something to rng
NullCell = somevalue
End Function

--
__________________________________
HTH

Bob

"John C" <johnc@stateofdenial wrote in message
...
What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a
basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.


--
John C

Some of the basics of UDFs are described at
http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"John C" <johnc@stateofdenial wrote in message
...
What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a
basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.


--
John C

On Wed, 6 Aug 2008 12:02:01 -0700, John C <johnc@stateofdenial wrote:

What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.

For the basic situation you can try the following UDF:

Function NullCell(r As Range) As Variant
If r.Value = "" Then
NullCell = ""
Else
NullCell = r.Value
End If
End Function

Hope this helps / Lars-Åke

Not quite as simple as that. I wish it were.
If A1 is either blank or empty, I want the cell where the NullCell function
is to appear empty.
If A1 is blank due to formula (i.e.: =""), then the NullCell function works,
but if there is NO data in there, I get an answer of 0.
--
John C


"Bob Phillips" wrote:

It is quite simple.

The value is returned as the function name

Function NullCell()

'some code
NullCell = somevalue
End Function

If you want to pass a value into the function you just declare an argument

Function NullCell(ByRef rng As Range)

'do something to rng
NullCell = somevalue
End Function

--
__________________________________
HTH

Bob

"John C" <johnc@stateofdenial wrote in message
...
What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a
basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.


--
John C

I come up with #VALUE! errors, no matter when tested. static text, static
number, formulated text, formulated number, empty cell by formula, empty cell
because it's just empty.
--
John C


"Lars-Ã…ke Aspelin" wrote:

On Wed, 6 Aug 2008 12:02:01 -0700, John C <johnc@stateofdenial wrote:

What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.

For the basic situation you can try the following UDF:

Function NullCell(r As Range) As Variant
If r.Value = "" Then
NullCell = ""
Else
NullCell = r.Value
End If
End Function

Hope this helps / Lars-Ã…ke

On Wed, 06 Aug 2008 19:47:00 GMT, Lars-Åke Aspelin
wrote:

On Wed, 6 Aug 2008 12:02:01 -0700, John C <johnc@stateofdenial wrote:

What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.

For the basic situation you can try the following UDF:

Function NullCell(r As Range) As Variant
If r.Value = "" Then
NullCell = ""
Else
NullCell = r.Value
End If
End Function

Hope this helps / Lars-Åke

Or why not just like this:

Function NullCell(r As Range) As Variant
NullCell = r.Value
End Function

On Wed, 06 Aug 2008 20:14:45 GMT, Lars-Åke Aspelin
wrote:

On Wed, 06 Aug 2008 19:47:00 GMT, Lars-Åke Aspelin
wrote:

On Wed, 6 Aug 2008 12:02:01 -0700, John C <johnc@stateofdenial wrote:

What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.

For the basic situation you can try the following UDF:

Function NullCell(r As Range) As Variant
If r.Value = "" Then
NullCell = ""
Else
NullCell = r.Value
End If
End Function

Hope this helps / Lars-Åke

Or why not just like this:

Function NullCell(r As Range) As Variant
NullCell = r.Value
End Function

No, that did not work. back to my first proposal:

Function NullCell(r As Range) As Variant
If r.Value = "" Then
NullCell = ""
Else
NullCell = r.Value
End If
End Function

Try something like:

Function NullCell(rng As Range) As Variant
If rng.Value = "" Then
NullCell = ""
Exit Function
End If
NullCell = rng.Value
End Function


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"John C" <johnc@stateofdenial wrote in message
...
Not quite as simple as that. I wish it were.
If A1 is either blank or empty, I want the cell where the NullCell
function
is to appear empty.
If A1 is blank due to formula (i.e.: =""), then the NullCell function
works,
but if there is NO data in there, I get an answer of 0.
--
John C


"Bob Phillips" wrote:

It is quite simple.

The value is returned as the function name

Function NullCell()

'some code
NullCell = somevalue
End Function

If you want to pass a value into the function you just declare an
argument

Function NullCell(ByRef rng As Range)

'do something to rng
NullCell = somevalue
End Function

--
__________________________________
HTH

Bob

"John C" <johnc@stateofdenial wrote in message
...
What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a
basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.


--
John C

#VALUE will be the result e.g. if you have not the same spelling of r
in (r as Range) and in r.Value

To me the function work as expected for all cases you mention.

Lars-Åke

On Wed, 6 Aug 2008 13:11:03 -0700, John C <johnc@stateofdenial wrote:

I come up with #VALUE! errors, no matter when tested. static text, static
number, formulated text, formulated number, empty cell by formula, empty cell
because it's just empty.

Thank you very much.
--
John C


"Sandy Mann" wrote:

Try something like:

Function NullCell(rng As Range) As Variant
If rng.Value = "" Then
NullCell = ""
Exit Function
End If
NullCell = rng.Value
End Function


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"John C" <johnc@stateofdenial wrote in message
...
Not quite as simple as that. I wish it were.
If A1 is either blank or empty, I want the cell where the NullCell
function
is to appear empty.
If A1 is blank due to formula (i.e.: =""), then the NullCell function
works,
but if there is NO data in there, I get an answer of 0.
--
John C


"Bob Phillips" wrote:

It is quite simple.

The value is returned as the function name

Function NullCell()

'some code
NullCell = somevalue
End Function

If you want to pass a value into the function you just declare an
argument

Function NullCell(ByRef rng As Range)

'do something to rng
NullCell = somevalue
End Function

--
__________________________________
HTH

Bob

"John C" <johnc@stateofdenial wrote in message
...
What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give a
basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.


--
John C

You're very welcome.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"John C" <johnc@stateofdenial wrote in message
...
Thank you very much.
--
John C


"Sandy Mann" wrote:

Try something like:

Function NullCell(rng As Range) As Variant
If rng.Value = "" Then
NullCell = ""
Exit Function
End If
NullCell = rng.Value
End Function


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"John C" <johnc@stateofdenial wrote in message
...
Not quite as simple as that. I wish it were.
If A1 is either blank or empty, I want the cell where the NullCell
function
is to appear empty.
If A1 is blank due to formula (i.e.: =""), then the NullCell function
works,
but if there is NO data in there, I get an answer of 0.
--
John C


"Bob Phillips" wrote:

It is quite simple.

The value is returned as the function name

Function NullCell()

'some code
NullCell = somevalue
End Function

If you want to pass a value into the function you just declare an
argument

Function NullCell(ByRef rng As Range)

'do something to rng
NullCell = somevalue
End Function

--
__________________________________
HTH

Bob

"John C" <johnc@stateofdenial wrote in message
...
What are some good references on creating UDF's?

While my real need is far more complex than I list here, I will give
a
basic
basic situation.
A2: =IF(A1="","",A1)

I would prefer something more like
A2: =NullCell(A1)

Thank you.


--
John C