Avatar of jnash67
jnash67

asked on 

Excel VBA - Format a string with the NumberFormat of a cell

I have some calculated values that depend on a selected range.  I want to format these calculated values with the NumberFormat of the selected range. I'm assuming they all have the same NumberFormat so I just take the numberformat of the first cell.

I am currently using the NumberFormat as follows:

columnFormat = cell.NumberFormat
stringNum = cstr(format(doubleNum, columnFormat)

Unfortunately, it doesn't work in a lot of cases, like when the format is "General" or a rather complex one we use that works fine in the cell but shows positive numbers ending with a "_)".

Is there a more robust way to do this?  Do I have to have a big list of exceptions to check for?
Visual Basic.NETMicrosoft Excel

Avatar of undefined
Last Comment
jnash67
Avatar of RichardSchollar
RichardSchollar
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi

You could use the WorksheetFunction.Text to do this (note this will be a little slower than using Format):

Richard

columnFormat = cell.NumberFormat
stringNum = cstr(WorksheetFunction.Text(doubleNum, columnFormat))

Open in new window

Avatar of jnash67
jnash67

ASKER

Do you still need the cstr() around it since WorksheetFunction.Text seems to convert the number to text already?
ASKER CERTIFIED SOLUTION
Avatar of RichardSchollar
RichardSchollar
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of jnash67
jnash67

ASKER

Thanks for speedy response
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo