UDF is not working

[Max]

Hi , i create UDF mypc which gives percent of the passed number as i need to
change the number format of given cell on some calculation,


function mypc(rng as long)
mypc=format(rng,"percent")
end function

Suppose In worksheet , A1 contains value 5

In B2, i type = mypc(A1) -> RETURNS 500%, but i am not able to perform
calculation on this returned values.

please help
Thanx

 

[Niek Otten]

Hi Max,

The returned value is text, with which you can't calculate directly.
You can use

=VALUE(mypc(A1))

or, if you want the format to show, =VALUE(B1) if the formula is in B1

In case you wanted to ask; you cannot format a cell from a function.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi , i create UDF mypc which gives percent of the passed number as i need to
| change the number format of given cell on some calculation,
|
|
| function mypc(rng as long)
| mypc=format(rng,"percent")
| end function
|
| Suppose In worksheet , A1 contains value 5
|
| In B2, i type = mypc(A1) -> RETURNS 500%, but i am not able to perform
| calculation on this returned values.
|
| please help
| Thanx

 

[Niek Otten]

<with which you can't calculate directly>

That's not entirely true. Some operations do accept text, but some functions fail. So it's better to convert it to a number using
VALUE or multiply it by 1, for example.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Max,
|
| The returned value is text, with which you can't calculate directly.
| You can use
|
| =VALUE(mypc(A1))
|
| or, if you want the format to show, =VALUE(B1) if the formula is in B1
|
| In case you wanted to ask; you cannot format a cell from a function.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|| Hi , i create UDF mypc which gives percent of the passed number as i need to
|| change the number format of given cell on some calculation,
||
||
|| function mypc(rng as long)
|| mypc=format(rng,"percent")
|| end function
||
|| Suppose In worksheet , A1 contains value 5
||
|| In B2, i type = mypc(A1) -> RETURNS 500%, but i am not able to perform
|| calculation on this returned values.
||
|| please help
|| Thanx
|
|