Excel keeps converting my %s to Time format

[Michael Deathya]

Hi,

I'm using Excel 97.

I have a vba function that puts values in specific cells. To do so, I'm
naming each cell and referencing it like so:

dim varMyPercent as variant
varMyPercent = 0.02
wb.Names("metric1000").RefersToRange.Value = varMyPercent

Here, the cell referred to by metric1000 is already formatted as a %
with 0 decimal points.

The problem is that Excel seems to (somewhat randomly) convert the cell
to a time format (e.g. 0.02 gets displayed as 12:28:48 AM)

I can do the above algorithm for 100 cells and perhaps 2-5 cells will
get converted display in the time format. There doesn't seem to be any
consistency as to how Excel does this and it's definitely not wanted.

Any ideas how to prevent this from happening? I tried prefixing the
value with an apostrophe but this causes Excel to ignore the formatting
in the cell.

Thanks,

Michael D

 

[Dave Peterson]

I'd make sure:

with wb.Names("metric1000").RefersToRange
.numberformat = "0.0%" 'or whatever
.Value = varMyPercent
end with

 

[Guest]

Hi,

Try ...

wb.Names("metric1000").RefersToRange.Numberformat="0%"
wb.Names("metric1000").RefersToRange.Value = varMyPercent


HTH

 

[Michael Deathya]

Thanks Dave,

That seems like it should work but I'd like to avoid it if possible.

I have almost 1000 such cells, each with their own format and I'd like
to be able have some flexibility without having the formats centrally
managed like that. I.e. if someone wants a number formatted
differently, they should be able to change it in the cell properties
without having to get involved with VBA.

Why can't Excel just leave the formats alone? It drives me nuts that I
can't type "1-3" without it converting it to Jan-3. Surely there must
be a way to turn off this 'feature'!

 

[Dave Peterson]

I've seen excel change formats from General to Date (or Time), but I've never
seen it change from Percent to time.

The way I turn the feature off for 1-3 becoming Jan-3 is to format the cell as
text (or add that leading apostrophe (both of which you don't like...)