Can not change the numberformat

[Guest]

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.

 

[BrianB]

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

 

[Guest]

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.

 

[Dave Peterson]

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"

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.