I try to change the cell number format in my "add in" module using following
statement:

ActiveCell.NumberFormat = "0.00%"

But it does not change the the format, the cell number format was still
"General".

here is my whole code:

Option Explicit

Function numberformat_test()
ActiveCell.NumberFormat = "0.00%"
numberformat_pc_test = 0.234
End Function

1. This code is save in test.xla as a add in file;
2. In Excel, from Tools-Add-Ins, load test.xla;
3. From Excel: Insert-Functions-User Defined category select
numberformat_test function;
4. The cell supposes present 23.4%; but it displays 0.234.


Some friends kindly offered some solutions, but they are only callable in
Macro and should be only resident in local sheet. What I want is an add-in
xla file which can be called by any sheet.

Thanks.

1. It is not possible to change workspace formatting etc. from within
function. Only a sub routine.

2. This cannot be all your code because you are using Option Explici
which requires declaration of all variables (in this cas
numberformat_pc_test).

3. Your basic function structure is incorrect. You have no argument(s
and no return value which must be the same name as the function

--
Brian

-----------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...tinfo&userid=5
View this thread: http://www.excelforum.com/showthread.php?threadid=27007

Thanks BrianB for your kind reply.

Yes, that is not all my code, I just post relative one and try to focus on
the problem.

How can I use sub routine to change the format in add-in xla file?

Cheers.

"BrianB" wrote:


1. It is not possible to change workspace formatting etc. from within a
function. Only a sub routine.

2. This cannot be all your code because you are using Option Explicit
which requires declaration of all variables (in this case
numberformat_pc_test).

3. Your basic function structure is incorrect. You have no argument(s)
and no return value which must be the same name as the function.


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=270075

You can select your range to change formats and do:

Option Explicit
Sub Changenumberformat_test()
Selection.NumberFormat = "0.00%"
End Sub

Then back to excel and Tools|macro|macros... and run "ChangeNumberFormat_test"



X.Yu wrote:

Thanks BrianB for your kind reply.

Yes, that is not all my code, I just post relative one and try to focus on
the problem.

How can I use sub routine to change the format in add-in xla file?

Cheers.

"BrianB" wrote:


1. It is not possible to change workspace formatting etc. from within a
function. Only a sub routine.

2. This cannot be all your code because you are using Option Explicit
which requires declaration of all variables (in this case
numberformat_pc_test).

3. Your basic function structure is incorrect. You have no argument(s)
and no return value which must be the same name as the function.


--
BrianB


------------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php...info&userid=55
View this thread: http://www.excelforum.com/showthread...hreadid=270075



--

Dave Peterson