Convert .NumberFormat Code to VBA Format Code

JonXL

Active Member
Joined
Feb 5, 2018
Messages
414
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello!

I am trying to figure out how I can convert the .NumberFormat string (eg, "_($* #,##0.00_)") to a string that can be used for the VBA Format() function (for this example it would be "$#,##0.00") to get an output formatted as close as possible to the original (taken from the cell using .Value) ignoring the color codes and spacing codes.

I've reviewed both the NumberFormat information as to possible codes and the Format information for the same. What I've come up with so far is that I would need to account for the _ and * plus the symbols that follow them. Also there would be some of the special symbols like > that would need to be rendered differently to ensure they don't do something they aren't supposed to... I'm wondering if there is anything else that needs to be accounted for OR even better, if someone is aware of a function that's already floating around the internet to do this that I can drop into my code without having to rewrite it. I searched and searched but because NumberFormat pages almost all also have the word Format and VBA in them, it'd be impossible for me to filter through the irrelevant results to find the ones I need so Googling hasn't gotten me too far.

Any help is appreciated!
Thank you,
Jon
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
Hi Jon
I am a little confused and i think I didn't understand you correctly because of the simplicity of the answer?
Is this what you are looking for?
Code:
Sheet1.Range("A:A").NumberFormat = "_($* #,##0.00_)"
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
414
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sorry. Let me explain better.

I have a cell's value and its NumberFormat. I'm going to take the value and put it in text outside of Excel and want to ensure it shows up in as similar a format as possible to how it looks to the user on the spreadsheet (I cannot collect the values with .Text).

So to set the format, I'm using the Format() function along with the formatting code returned from NumberFormat. The issue is that the coding used for NumberFormat is not the same as the coding used for Format() so I can't just take the cell's NumberFormat and throw it in the Format() function (eg, x = Format(ValueFromCell, CellsNumberFormatCode)). The code returned from NumberFormat needs to be 'translated' first to a formatting code sensible to the Format() function. Or perhaps there is another way to grab or set the format that doesn't require this translation (using functions and/or properties I'm not aware of?)...

I hope this is clearer.

Thank you,
Jon
 

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
Hi Jon
Thanks, that makes it clearer
Just 1 quuestion. Why can you not use .Text?

#Excel Fan
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
414
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Jon
Thanks, that makes it clearer
Just 1 quuestion. Why can you not use .Text?

I am reading a ton of cells into an array.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
I am reading a ton of cells into an array.

Assuming there are no cells that has multiline content & you work with one column only, one way to populate text into an array (without looping each cell) is using clipboard.
So copy the range > get the clipboard > populate into array using Split with vbCrLf as separator.
Something like this:

VBA Code:
Sub a1157692a()

Dim obj As New DataObject
Dim tx As String, ary

Range("A1:A10").Copy
obj.GetFromClipboard
tx = obj.GetText
ary = Split(tx, vbCrLf)

End Sub

Note: you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
414
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Assuming there are no cells that has multiline content & you work with one column only, one way to populate text into an array (without looping each cell) is using clipboard.

Thank you for the suggestion but I'm pretty averse to using the clipboard "behind the scenes". And I can't really guarantee cells won't have line breaks in them anyway.

Right now as close as I can get is either converting the sheet to text or converting the array values with something like myArray(1,1) = Application.WorksheetFunction.Text(myArray(1,1),NumberFormatCode).

But both methods add considerably to my prep time (like minutes...) so I'd like to find something else. I'll keep poking at it...

Thank you. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,079
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top