Convert .NumberFormat Code to VBA Format Code

[JonXL]

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

 

[EFANYoutube]

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?

Sheet1.Range("A:A").NumberFormat = "_($* #,##0.00_)"

 

[JonXL]

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]

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

#Excel Fan

 

[JonXL]

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]

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:

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]

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.