Numberformat applied but does not change data

[Tony Girgenti]

Hello.

I'm using VB6.0, SP6 and Excel 2003, SP3.

I'm formatting a worksheet column and then filling the columns with data.
The format gets applied, but the data is not appearing as expected. These
lines of code are what i am using to format the column with a Percent.

newExcelWorkSheet.Columns("D").NumberFormat = "0.00%"
newExcelWorkSheet.Columns("D").HorizontalAlignment = xlCenter

When i open the Excel workbook, the data is there and those cells show it
formatted as Percent with 2 decimals. The data is sent to the value of the
cell as "015.00"(without the quotes), yet when open the workbook, it appears
as 015.00. I was expecting it to appear as 15.00%.

The HorizontalAlignment works. Why is the data not appearing as a percent?

If i open the workbook and put 015.00 in one of the formatted cells, it does
appear as 15.00%.

Any help would be gratefully appreciated.

Thanks,
Tony

 

[JLGWhiz]

Try this:
newExcelWorkSheet.Columns("D").NumberFormat = "#0.00%"

 

[JLGWhiz]

You can disregard the previous posting. I just noticed an odd occurrence.
If I manually enter 15 or .15 either will give me 15% displayed. However, if
I enter the amount in code as 15 it gives me 1500% and 0.15 gives me 15%
displayed. This has to be an Excel/VBA bug. I am going to check the
Microsoft site for any alerts on this.

 

[Tony Girgenti]

Hello JLGWhiz.

I'm not using VBA, but thanks for anything that you can find. Very much
appreciated.

Tony

 

[JLGWhiz]

Yes, I understand that you are using VB6, however, Excel doesn't change.
What I found is that Excel has built in features for manual entries of
percentages. If the number is >= 1 then it simply attaches the % symbol. If
the number is < 1 but > 0 then it multiplies by 100 and displays as a
percentage. Now... If the value is entered by code and the cell is formatted
for percentage it multiplies everything by 100.

This does not resolve the fact that your display did not show the percent
symbol.
So, if you are sending 015.00 to the cell it must be as a string variable
since it would otherwise be converted to 15 as an integer. When I tried
that, It converted the string to 1500 just like the number 15.

So the conclusion is, I don't know why you are getting that result since I
can't duplicate it.

 

[Charlie]

The VB is correct: 15 is 1500% and .15 is 15%. It is one of those quirky,
and sometimes annoying, functions of entering values into cells. Excel tries
to outguess us. Typing in whole numbers Excel decideds that we are typing in
the percent (see the percent sign appear as you type?) Type in a decimal
point first and Excel decides we are entering the actual numeric value.
Great huh?

In electrical wiring there is an annotation for cables. (I'm not an
electrical engineer), annotation examples are 1/0 (one-ought), 2/0
(two-ought), 4/0 (four-ought), etc., which represents number of wires and
wire size (I think.) Try typing that in. Excel thinks I'm typing a date!
I'm trying to help an EE solve this pain-in-the-butt problem. Changing the
cell to Text causes VLOOKUP errors elsewhere.

 

[Tony Girgenti]

Thanks to Charlie and JLGWhiz for their help.

I sure wish i could figure this out. Somebody has to have an answer. I'm
sure this problem has come up before.

Thanks,
Tony

 

[Tony Girgenti]

Just checking to see if my latest reply is updated.

 

[Tony Girgenti]

This is a test to see if my replies are getting posted.

I posted replies to this thread using Outlook Express, but they are not
showing up when i view these newsgroups using Internet Explorer Microsoft
Discussion Groups at:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

They do show up in Outlook Express.

Can anybody explain that?

Anyway, here is what i wanted to say:
Thanks to Charlie and JLGWhiz for their help.

I sure wish i could figure this out. Somebody has to have an answer. I'm
sure this problem has come up before.

Thanks,
Tony

 

[Tony Girgenti]

Just checking to see if my latest reply is updated. I posted replies to this
thread using Outlook Express, but they are not appearing when using the
Internet Explorer Microsoft Discussion Groups. They do appear in my OE.

Can anybody explain that?

Anyway, here is what i wanted to say:

Thanks to Charlie and JLGWhiz for their help.

I sure wish i could figure this out. Somebody has to have an answer. I'm
sure this problem has come up before.

Thanks,
Tony

 

[Tony Girgenti]

This is a test to see if my replies are getting posted.

I posted replies to this thread using Outlook Express, but they are not
showing up when i view these newsgroups using Internet Explorer Microsoft
Discussion Groups at:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

They do show up in Outlook Express.

Can anybody explain that?

Anyway, here is what i wanted to say:
Thanks to Charlie and JLGWhiz for their help.

I sure wish i could figure this out. Somebody has to have an answer. I'm
sure this problem has come up before.

Thanks,
Tony

 

[Tony Girgenti]

Does anybody have a solution for this?

I really need it. I have been searching and trying to get this resolved for
days. I can't believe that i am the only one that ever had a problem
formatting numerics in a newly created Excel worksheet through VB 6.

I'm ready to turn this thing over to the client soon and it will not have
the numeric fields formatted properly.

Nobody else has had this problem in VB6? Where they put a string of numbers
into a cell and used the Numberformat = "###,##0.00_);[Red](###,##0.00)"
property. Then when they open the workbook the numbers are *not* formatted.

I guess my last resort would be to pay Microsoft support to help me rosolve
it.

Any help would be gratefully appreciated.

Thanks,
Tony