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

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" <jamescornthwaite@btinternet.com> wrote in message
news:Y_mdnbzuMbUsMAjZRVnyug@bt.com...
>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
>

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" <bartsmissaert@blueyonder.co.uk> wrote in message
news:%23ANgqIxkGHA.4044@TK2MSFTNGP03.phx.gbl...
> 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" <jamescornthwaite@btinternet.com> wrote in message
> news:Y_mdnbzuMbUsMAjZRVnyug@bt.com...
>>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
>>
>

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" <jamescornthwaite@btinternet.com> wrote in message
news:cZudndOX8aX8LgjZnZ2dnUVZ8sydnZ2d@bt.com...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%23ANgqIxkGHA.4044@TK2MSFTNGP03.phx.gbl...
>> 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" <jamescornthwaite@btinternet.com> wrote in message
>> news:Y_mdnbzuMbUsMAjZRVnyug@bt.com...
>>>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
>>>
>>
>
>

great thanks

"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:%23jMm9UxkGHA.3572@TK2MSFTNGP04.phx.gbl...
> 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" <jamescornthwaite@btinternet.com> wrote in message
> news:cZudndOX8aX8LgjZnZ2dnUVZ8sydnZ2d@bt.com...
>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
>> news:%23ANgqIxkGHA.4044@TK2MSFTNGP03.phx.gbl...
>>> 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" <jamescornthwaite@btinternet.com> wrote in message
>>> news:Y_mdnbzuMbUsMAjZRVnyug@bt.com...
>>>>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
>>>>
>>>
>>
>>
>