PC Review


Reply
Thread Tools Rate Thread

Custom Number Formats in Code

 
 
=?Utf-8?B?dmVyeWVhdnk=?=
Guest
Posts: n/a
 
      21st Nov 2006
Hi,

Hope this qualifies as "programming".

the following macro:

Selection.NumberFormat = "#,##0_);(#,##0)"

does not do the job I was hoping.

On examination the format that is applied is:

#,##0;-#,##0

any way to get my preferred format to "take"?

TIA, Matt
 
Reply With Quote
 
 
 
 
=?Utf-8?B?dmVyeWVhdnk=?=
Guest
Posts: n/a
 
      21st Nov 2006
Further Developments :-)

Irony of Ironies - this is the example in the Help system:

Example
These examples set the number format for cell A17, row one, and column C
(respectively) on Sheet1.

Worksheets("Sheet1").Range("A17").NumberFormat = "General"
Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
Worksheets("Sheet1").Columns("C"). _
NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

and this doesn't work either!

My colleague very helpfully suggested using "Styles" and while this should
be workable this is starting to turn something that should be super-neat and
tidy into something more cumbersome (having to create the style
programmatically before applying it).

So suggestions as to why the simple approach isn't working are still sought.

TIA and Cheers, Matt



"veryeavy" wrote:

> Hi,
>
> Hope this qualifies as "programming".
>
> the following macro:
>
> Selection.NumberFormat = "#,##0_);(#,##0)"
>
> does not do the job I was hoping.
>
> On examination the format that is applied is:
>
> #,##0;-#,##0
>
> any way to get my preferred format to "take"?
>
> TIA, Matt

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      21st Nov 2006
Excel depends on somethings from windows.

Try changing the negative currency format under windows regional settings (under
control panel) to:
($1.1)

When I changed it to:
-$1.1

I had the same problem as you.

And I had to close excel and reopen to see the difference.

veryeavy wrote:
>
> Further Developments :-)
>
> Irony of Ironies - this is the example in the Help system:
>
> Example
> These examples set the number format for cell A17, row one, and column C
> (respectively) on Sheet1.
>
> Worksheets("Sheet1").Range("A17").NumberFormat = "General"
> Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
> Worksheets("Sheet1").Columns("C"). _
> NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
>
> and this doesn't work either!
>
> My colleague very helpfully suggested using "Styles" and while this should
> be workable this is starting to turn something that should be super-neat and
> tidy into something more cumbersome (having to create the style
> programmatically before applying it).
>
> So suggestions as to why the simple approach isn't working are still sought.
>
> TIA and Cheers, Matt
>
> "veryeavy" wrote:
>
> > Hi,
> >
> > Hope this qualifies as "programming".
> >
> > the following macro:
> >
> > Selection.NumberFormat = "#,##0_);(#,##0)"
> >
> > does not do the job I was hoping.
> >
> > On examination the format that is applied is:
> >
> > #,##0;-#,##0
> >
> > any way to get my preferred format to "take"?
> >
> > TIA, Matt


--

Dave Peterson
 
Reply With Quote
 
Gary Keramidas
Guest
Posts: n/a
 
      21st Nov 2006
it works for me.

in a blank worksheet, enter a number in a1 and make sure it's selected
then in the vb editor open the immediate window (view/immediate window or
ctrl-G)

then paste your code in there, place your cursor on the line of code and hit
enter: Selection.NumberFormat = "#,##0_);(#,##0)"

does it format your number correctly?




--


Gary


"veryeavy" <(E-Mail Removed)> wrote in message
news:4CB87E05-AD81-4342-AB11-(E-Mail Removed)...
> Hi,
>
> Hope this qualifies as "programming".
>
> the following macro:
>
> Selection.NumberFormat = "#,##0_);(#,##0)"
>
> does not do the job I was hoping.
>
> On examination the format that is applied is:
>
> #,##0;-#,##0
>
> any way to get my preferred format to "take"?
>
> TIA, Matt



 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      21st Nov 2006
You may not have to close Excel and reopen--I may have not hit the Apply button
(oopsie!).

veryeavy wrote:
>
> Further Developments :-)
>
> Irony of Ironies - this is the example in the Help system:
>
> Example
> These examples set the number format for cell A17, row one, and column C
> (respectively) on Sheet1.
>
> Worksheets("Sheet1").Range("A17").NumberFormat = "General"
> Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
> Worksheets("Sheet1").Columns("C"). _
> NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
>
> and this doesn't work either!
>
> My colleague very helpfully suggested using "Styles" and while this should
> be workable this is starting to turn something that should be super-neat and
> tidy into something more cumbersome (having to create the style
> programmatically before applying it).
>
> So suggestions as to why the simple approach isn't working are still sought.
>
> TIA and Cheers, Matt
>
> "veryeavy" wrote:
>
> > Hi,
> >
> > Hope this qualifies as "programming".
> >
> > the following macro:
> >
> > Selection.NumberFormat = "#,##0_);(#,##0)"
> >
> > does not do the job I was hoping.
> >
> > On examination the format that is applied is:
> >
> > #,##0;-#,##0
> >
> > any way to get my preferred format to "take"?
> >
> > TIA, Matt


--

Dave Peterson
 
Reply With Quote
 
=?Utf-8?B?dmVyeWVhdnk=?=
Guest
Posts: n/a
 
      21st Nov 2006
No.

Can I expect to get differing results via the immediate window rather than
the macro? This is quite literally a one line macro so I wouldn't have
expected a different result ...

Was worth a try tho' :-)

Thx

"Gary Keramidas" wrote:

> it works for me.
>
> in a blank worksheet, enter a number in a1 and make sure it's selected
> then in the vb editor open the immediate window (view/immediate window or
> ctrl-G)
>
> then paste your code in there, place your cursor on the line of code and hit
> enter: Selection.NumberFormat = "#,##0_);(#,##0)"
>
> does it format your number correctly?
>
>
>
>
> --
>
>
> Gary
>
>
> "veryeavy" <(E-Mail Removed)> wrote in message
> news:4CB87E05-AD81-4342-AB11-(E-Mail Removed)...
> > Hi,
> >
> > Hope this qualifies as "programming".
> >
> > the following macro:
> >
> > Selection.NumberFormat = "#,##0_);(#,##0)"
> >
> > does not do the job I was hoping.
> >
> > On examination the format that is applied is:
> >
> > #,##0;-#,##0
> >
> > any way to get my preferred format to "take"?
> >
> > TIA, Matt

>
>
>

 
Reply With Quote
 
=?Utf-8?B?dmVyeWVhdnk=?=
Guest
Posts: n/a
 
      21st Nov 2006
Hi Dave,

I don't seem to have a specific negative currency option.

My Options are Number: and Currency:

and are set to:

123,456,789.00 and $123,456,789.00 respectively.

I am running Excel 2003 SP1 on Windows XP Professional SP2.

TIAA :-)

Matt

"Dave Peterson" wrote:

> Excel depends on somethings from windows.
>
> Try changing the negative currency format under windows regional settings (under
> control panel) to:
> ($1.1)
>
> When I changed it to:
> -$1.1
>
> I had the same problem as you.
>
> And I had to close excel and reopen to see the difference.
>
> veryeavy wrote:
> >
> > Further Developments :-)
> >
> > Irony of Ironies - this is the example in the Help system:
> >
> > Example
> > These examples set the number format for cell A17, row one, and column C
> > (respectively) on Sheet1.
> >
> > Worksheets("Sheet1").Range("A17").NumberFormat = "General"
> > Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
> > Worksheets("Sheet1").Columns("C"). _
> > NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
> >
> > and this doesn't work either!
> >
> > My colleague very helpfully suggested using "Styles" and while this should
> > be workable this is starting to turn something that should be super-neat and
> > tidy into something more cumbersome (having to create the style
> > programmatically before applying it).
> >
> > So suggestions as to why the simple approach isn't working are still sought.
> >
> > TIA and Cheers, Matt
> >
> > "veryeavy" wrote:
> >
> > > Hi,
> > >
> > > Hope this qualifies as "programming".
> > >
> > > the following macro:
> > >
> > > Selection.NumberFormat = "#,##0_);(#,##0)"
> > >
> > > does not do the job I was hoping.
> > >
> > > On examination the format that is applied is:
> > >
> > > #,##0;-#,##0
> > >
> > > any way to get my preferred format to "take"?
> > >
> > > TIA, Matt

>
> --
>
> Dave Peterson
>

 
Reply With Quote
 
Dana DeLouis
Guest
Posts: n/a
 
      21st Nov 2006
Your code worked fine for me also.
Does this small code help in debugging.
It returns the current format, what it should be, and what it was changed
to.

Sub Test()
Const s As String = "#,##0_);(#,##0)"

Debug.Print Range("A1").NumberFormat
Range("A1").NumberFormat = s

Debug.Print s
Debug.Print Range("A1").NumberFormat
End Sub

For me, it returned:

General
#,##0_);(#,##0)
#,##0_);(#,##0)

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


"veryeavy" <(E-Mail Removed)> wrote in message
news:4CB87E05-AD81-4342-AB11-(E-Mail Removed)...
> Hi,
>
> Hope this qualifies as "programming".
>
> the following macro:
>
> Selection.NumberFormat = "#,##0_);(#,##0)"
>
> does not do the job I was hoping.
>
> On examination the format that is applied is:
>
> #,##0;-#,##0
>
> any way to get my preferred format to "take"?
>
> TIA, Matt



 
Reply With Quote
 
Mike Middleton
Guest
Posts: n/a
 
      21st Nov 2006
veryeavy -

> Can I expect to get differing results via the immediate window rather than
> the macro? <


Yes, you can expect different formatting in the VBE immediate window
compared with the formatting in a cell in an Excel worksheet.

- Mike
http://www.mikemiddleton.com


 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      21st Nov 2006
In WinXP Home, I can get to the Control panel
then Regional and language options
then click the customize button
On the currency tab, I see "negative currency format" as the 3rd dropdown.


veryeavy wrote:
>
> Hi Dave,
>
> I don't seem to have a specific negative currency option.
>
> My Options are Number: and Currency:
>
> and are set to:
>
> 123,456,789.00 and $123,456,789.00 respectively.
>
> I am running Excel 2003 SP1 on Windows XP Professional SP2.
>
> TIAA :-)
>
> Matt
>
> "Dave Peterson" wrote:
>
> > Excel depends on somethings from windows.
> >
> > Try changing the negative currency format under windows regional settings (under
> > control panel) to:
> > ($1.1)
> >
> > When I changed it to:
> > -$1.1
> >
> > I had the same problem as you.
> >
> > And I had to close excel and reopen to see the difference.
> >
> > veryeavy wrote:
> > >
> > > Further Developments :-)
> > >
> > > Irony of Ironies - this is the example in the Help system:
> > >
> > > Example
> > > These examples set the number format for cell A17, row one, and column C
> > > (respectively) on Sheet1.
> > >
> > > Worksheets("Sheet1").Range("A17").NumberFormat = "General"
> > > Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
> > > Worksheets("Sheet1").Columns("C"). _
> > > NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
> > >
> > > and this doesn't work either!
> > >
> > > My colleague very helpfully suggested using "Styles" and while this should
> > > be workable this is starting to turn something that should be super-neat and
> > > tidy into something more cumbersome (having to create the style
> > > programmatically before applying it).
> > >
> > > So suggestions as to why the simple approach isn't working are still sought.
> > >
> > > TIA and Cheers, Matt
> > >
> > > "veryeavy" wrote:
> > >
> > > > Hi,
> > > >
> > > > Hope this qualifies as "programming".
> > > >
> > > > the following macro:
> > > >
> > > > Selection.NumberFormat = "#,##0_);(#,##0)"
> > > >
> > > > does not do the job I was hoping.
> > > >
> > > > On examination the format that is applied is:
> > > >
> > > > #,##0;-#,##0
> > > >
> > > > any way to get my preferred format to "take"?
> > > >
> > > > TIA, Matt

> >
> > --
> >
> > Dave Peterson
> >


--

Dave Peterson
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Number Formats wjtmcd Microsoft Excel Programming 1 10th Jul 2006 11:47 PM
Custom Number Formats - Help =?Utf-8?B?SmFtZXMgSGFtaWx0b24=?= Microsoft Excel Misc 3 16th May 2006 04:10 PM
[$-409] in Custom Number Formats Conan Kelly Microsoft Excel Discussion 1 30th Mar 2006 03:24 AM
Custom Number Formats =?Utf-8?B?TWFyeSBBbm4=?= Microsoft Excel Misc 8 11th Jul 2005 07:56 PM
Custom Number formats Mel Microsoft Excel Worksheet Functions 1 9th Jul 2003 08:48 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:32 AM.