Formatting numbers

  • Thread starter Thread starter fred
  • Start date Start date
F

fred

Hello,
When using Excel automation, how to programmatically format all
numbers in the specified column as the phone numbers.
I can see it in the Excel menu there is possible to do Format>Special>Phone
Numbers.
How to do that programmatically?
Thanks,
Fred
 
Not sure how you want to implement this, but the following should give you
an idea how to proceed...

Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"
 
Thank you, Rick
When I try this:
moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####"
there is a compile error:
"Expected: identifier or bracketed expression"

I wonder, if there is way to "preformat" the whole column, so when the
numbers are entered into it they will be automatically formatted as the
phone numbers?
Thanks,
Fred

Rick Rothstein said:
Not sure how you want to implement this, but the following should give you
an idea how to proceed...

Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"

--
Rick (MVP - Excel)


fred said:
Hello,
When using Excel automation, how to programmatically format all
numbers in the specified column as the phone numbers.
I can see it in the Excel menu there is possible to do
Format>Special>Phone Numbers.
How to do that programmatically?
Thanks,
Fred
Click to expand...
Click to expand...
 
Sorry, my fault. Syntax error.
When I try this:
moExcelWS.Cells(3,3).NumberFormat = "[<=9999999]###-####;(###) ###-####"
the response is (True/False) which indicates to me that it is comparing the
formats not introducing it.
Fred

fred said:
Thank you, Rick
When I try this:
moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####"
there is a compile error:
"Expected: identifier or bracketed expression"

I wonder, if there is way to "preformat" the whole column, so when the
numbers are entered into it they will be automatically formatted as the
phone numbers?
Thanks,
Fred

Rick Rothstein said:
Not sure how you want to implement this, but the following should give
you an idea how to proceed...

Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"

--
Rick (MVP - Excel)


fred said:
Hello,
When using Excel automation, how to programmatically format all
numbers in the specified column as the phone numbers.
I can see it in the Excel menu there is possible to do
Format>Special>Phone Numbers.
How to do that programmatically?
Thanks,
Fred
Click to expand...
Click to expand...
Click to expand...
 
moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####"

First off, I assume the missing "dot" that should be in front of
"NumberFormat" is a typo. What is moExcelWS... a variable containing the
worksheet name or a variable containing a reference to the worksheet? If the
former, you would need to make it an argument to the Worksheet object
call...

Worksheets(moExcelWS).Cells(3,3).NumberFormat=....etc
I wonder, if there is way to "preformat" the whole column, so when the
numbers are entered into it they will be automatically formatted as the
phone numbers?
Click to expand...

Do you mean in code (as opposed to selecting the column and using
Cell/Format on the worksheet directly)? If so, you should be able to do
something like this in code...

Columns("G").NumberFormat = "[<=9999999]###-####;(###) ###-####"

Where you would change the column reference to suit your needs and preface
it with a worksheet reference.

--
Rick (MVP - Excel)


fred said:
Thank you, Rick
When I try this:
moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####"
there is a compile error:
"Expected: identifier or bracketed expression"

I wonder, if there is way to "preformat" the whole column, so when the
numbers are entered into it they will be automatically formatted as the
phone numbers?
Thanks,
Fred

Rick Rothstein said:
Not sure how you want to implement this, but the following should give
you an idea how to proceed...

Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"

--
Rick (MVP - Excel)


fred said:
Hello,
When using Excel automation, how to programmatically format all
numbers in the specified column as the phone numbers.
I can see it in the Excel menu there is possible to do
Format>Special>Phone Numbers.
How to do that programmatically?
Thanks,
Fred
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure what you mean by "the response". If you execute that line, as
written, it should perform the format operation... if you include as part of
a larger line of code, well, then I'd have to see exactly how you are trying
to use it in order to make a guess at what is happening.

--
Rick (MVP - Excel)


fred said:
Sorry, my fault. Syntax error.
When I try this:
moExcelWS.Cells(3,3).NumberFormat = "[<=9999999]###-####;(###) ###-####"
the response is (True/False) which indicates to me that it is comparing
the formats not introducing it.
Fred

fred said:
Thank you, Rick
When I try this:
moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####"
there is a compile error:
"Expected: identifier or bracketed expression"

I wonder, if there is way to "preformat" the whole column, so when the
numbers are entered into it they will be automatically formatted as the
phone numbers?
Thanks,
Fred

Rick Rothstein said:
Not sure how you want to implement this, but the following should give
you an idea how to proceed...

Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"

--
Rick (MVP - Excel)


Hello,
When using Excel automation, how to programmatically format
all numbers in the specified column as the phone numbers.
I can see it in the Excel menu there is possible to do
Format>Special>Phone Numbers.
How to do that programmatically?
Thanks,
Fred
Click to expand...
Click to expand...
Click to expand...
 
Thank you, Rick
moExcelWS.Columns("c").NumberFormat = "[<=9999999]###-####;(###) ###-####"
does work!
It formats all values in that column.
Fred

Rick Rothstein said:
moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####"
Click to expand...

First off, I assume the missing "dot" that should be in front of
"NumberFormat" is a typo. What is moExcelWS... a variable containing the
worksheet name or a variable containing a reference to the worksheet? If
the former, you would need to make it an argument to the Worksheet object
call...

Worksheets(moExcelWS).Cells(3,3).NumberFormat=....etc
I wonder, if there is way to "preformat" the whole column, so when the
numbers are entered into it they will be automatically formatted as the
phone numbers?
Click to expand...

Do you mean in code (as opposed to selecting the column and using
Cell/Format on the worksheet directly)? If so, you should be able to do
something like this in code...

Columns("G").NumberFormat = "[<=9999999]###-####;(###) ###-####"

Where you would change the column reference to suit your needs and preface
it with a worksheet reference.

--
Rick (MVP - Excel)


fred said:
Thank you, Rick
When I try this:
moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####"
there is a compile error:
"Expected: identifier or bracketed expression"

I wonder, if there is way to "preformat" the whole column, so when the
numbers are entered into it they will be automatically formatted as the
phone numbers?
Thanks,
Fred

Rick Rothstein said:
Not sure how you want to implement this, but the following should give
you an idea how to proceed...

Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####"

--
Rick (MVP - Excel)


Hello,
When using Excel automation, how to programmatically format
all numbers in the specified column as the phone numbers.
I can see it in the Excel menu there is possible to do
Format>Special>Phone Numbers.
How to do that programmatically?
Thanks,
Fred
Click to expand...
Click to expand...
Click to expand...
 
One more question:
I am loading very long list of numbers, (one column, loading in the loop row
by row).
For the optimum speed:
should I preformat the column before loading the numbers or format the whole
column after the loading is done?
What do you recommend?
Thanks,
Fred
 
I've never really thought about it... my gut feeling says it probably
doesn't matter which order you do those in.
 
Back
Top