Tricky one ( variant type text to be unaffected by a minus)
 

I have written a function


findNewNominal(NomCode as Integer) as Variant

if x exists in a certain range
findNewNominal = an integer corresponding to nomCode

else
findNewNominal = "Nominal Code does not exist"


So in conclusion my variant is either an integer or text



Now when calling this function (i never know in advance whether text or an
int will be returned)
I sometimes want the negative result of the integer returned.


So in some cells i say = - findNewNominal(a number)

This fine provided an integer is returned.
However I want it obvious when the function reports a nominal code could not
be found. The
problem is the '-' turns the text (Nominal Code Not Found) to a 0 (so it
looks like the integer zero has been returned when in fact the nominal
code couldnt be found.

Is there anyway of returning a variant type in my function when if it text,
it is still displayed as such and not
effected by the preceding minus to the function call.

(i cannot really remove the minus, as if an int is to be returned i need on
certain occassions the reverse sign of this)

Many thanks
James

Tricky one ( variant type text to be unaffected by a minus)
 

How about making the return of the function an integer or a long data type.
If x exists return the positive number if it doesn't return -1.
The calling procedure will then know if x existed or not and can act
accordingly.

RBS

"James Cornthwaite" wrote in message
...
I have written a function


findNewNominal(NomCode as Integer) as Variant

if x exists in a certain range
findNewNominal = an integer corresponding to nomCode

else
findNewNominal = "Nominal Code does not exist"


So in conclusion my variant is either an integer or text



Now when calling this function (i never know in advance whether text or an
int will be returned)
I sometimes want the negative result of the integer returned.


So in some cells i say = - findNewNominal(a number)

This fine provided an integer is returned.
However I want it obvious when the function reports a nominal code could
not be found. The
problem is the '-' turns the text (Nominal Code Not Found) to a 0 (so it
looks like the integer zero has been returned when in fact the nominal
code couldnt be found.

Is there anyway of returning a variant type in my function when if it
text, it is still displayed as such and not
effected by the preceding minus to the function call.

(i cannot really remove the minus, as if an int is to be returned i need
on certain occassions the reverse sign of this)

Many thanks
James

Tricky one ( variant type text to be unaffected by a minus)
 

Unfortunately its a UDF, and i'm calling it from a presentation worksheet
and only human know how can be used to decide if a -ve is needed.

Bit complicated to explain (but know what your saying). Won't really work in
this situation though.Does minus have to format text?

"RB Smissaert" wrote in message
...
How about making the return of the function an integer or a long data
type.
If x exists return the positive number if it doesn't return -1.
The calling procedure will then know if x existed or not and can act
accordingly.

RBS

"James Cornthwaite" wrote in message
...
I have written a function


findNewNominal(NomCode as Integer) as Variant

if x exists in a certain range
findNewNominal = an integer corresponding to nomCode

else
findNewNominal = "Nominal Code does not exist"


So in conclusion my variant is either an integer or text



Now when calling this function (i never know in advance whether text or
an int will be returned)
I sometimes want the negative result of the integer returned.


So in some cells i say = - findNewNominal(a number)

This fine provided an integer is returned.
However I want it obvious when the function reports a nominal code could
not be found. The
problem is the '-' turns the text (Nominal Code Not Found) to a 0 (so it
looks like the integer zero has been returned when in fact the nominal
code couldnt be found.

Is there anyway of returning a variant type in my function when if it
text, it is still displayed as such and not
effected by the preceding minus to the function call.

(i cannot really remove the minus, as if an int is to be returned i need
on certain occassions the reverse sign of this)

Many thanks
James

Tricky one ( variant type text to be unaffected by a minus)
 

OK, 2 other options:
1. Do IsNumeric on the return value.
2. Make the function return a variant array with 2 elements.
First element is the number or the text: "Nominal Code does not exist"
Second element is a boolean value indicating if x existed or not.

RBS

"James Cornthwaite" wrote in message
...
Unfortunately its a UDF, and i'm calling it from a presentation worksheet
and only human know how can be used to decide if a -ve is needed.

Bit complicated to explain (but know what your saying). Won't really work
in this situation though.Does minus have to format text?

"RB Smissaert" wrote in message
...
How about making the return of the function an integer or a long data
type.
If x exists return the positive number if it doesn't return -1.
The calling procedure will then know if x existed or not and can act
accordingly.

RBS

"James Cornthwaite" wrote in message
...
I have written a function


findNewNominal(NomCode as Integer) as Variant

if x exists in a certain range
findNewNominal = an integer corresponding to nomCode

else
findNewNominal = "Nominal Code does not exist"


So in conclusion my variant is either an integer or text



Now when calling this function (i never know in advance whether text or
an int will be returned)
I sometimes want the negative result of the integer returned.


So in some cells i say = - findNewNominal(a number)

This fine provided an integer is returned.
However I want it obvious when the function reports a nominal code could
not be found. The
problem is the '-' turns the text (Nominal Code Not Found) to a 0 (so it
looks like the integer zero has been returned when in fact the nominal
code couldnt be found.

Is there anyway of returning a variant type in my function when if it
text, it is still displayed as such and not
effected by the preceding minus to the function call.

(i cannot really remove the minus, as if an int is to be returned i need
on certain occassions the reverse sign of this)

Many thanks
James

Tricky one ( variant type text to be unaffected by a minus)
 

great thanks

"RB Smissaert" wrote in message
...
OK, 2 other options:
1. Do IsNumeric on the return value.
2. Make the function return a variant array with 2 elements.
First element is the number or the text: "Nominal Code does not exist"
Second element is a boolean value indicating if x existed or not.

RBS

"James Cornthwaite" wrote in message
...
Unfortunately its a UDF, and i'm calling it from a presentation worksheet
and only human know how can be used to decide if a -ve is needed.

Bit complicated to explain (but know what your saying). Won't really work
in this situation though.Does minus have to format text?

"RB Smissaert" wrote in message
...
How about making the return of the function an integer or a long data
type.
If x exists return the positive number if it doesn't return -1.
The calling procedure will then know if x existed or not and can act
accordingly.

RBS

"James Cornthwaite" wrote in message
...
I have written a function


findNewNominal(NomCode as Integer) as Variant

if x exists in a certain range
findNewNominal = an integer corresponding to nomCode

else
findNewNominal = "Nominal Code does not exist"


So in conclusion my variant is either an integer or text



Now when calling this function (i never know in advance whether text or
an int will be returned)
I sometimes want the negative result of the integer returned.


So in some cells i say = - findNewNominal(a number)

This fine provided an integer is returned.
However I want it obvious when the function reports a nominal code
could not be found. The
problem is the '-' turns the text (Nominal Code Not Found) to a 0 (so
it looks like the integer zero has been returned when in fact the
nominal
code couldnt be found.

Is there anyway of returning a variant type in my function when if it
text, it is still displayed as such and not
effected by the preceding minus to the function call.

(i cannot really remove the minus, as if an int is to be returned i
need on certain occassions the reverse sign of this)

Many thanks
James