Get formatted value/text of a Range without looping

[tttmack]

I'd like to be able to efficiently read all of the formatted values of a range of cells into an array. When I say formatted values, I mean the values as they are shown on the screen.

Range.Value and Range.Value2 can efficiently retrieve the values into an array.

Range.Text seems to work only on a single cell at a time and additionally the column needs to be autofitted first or you may get something like #### if the column is too small to display everything.

The problem with Range.Text is that it involves looping and while it is slow in VBA, it is extremely slow in VSTO.

Is there any other way that I can retrieve the text as it appears on screen efficiently using some other property or trick?

Note that Range.NumberFormat also doesn't work for multiple cells unless they all have the same NumberFormat. So a solution of using NumberFormat along side Value or Value2 is not feasible either since NumberFormat would involve looping.

 

[StephenCrump]

Will this work for you?

'Careful - loss of accuracy is permanent
ActiveWorkbook.PrecisionAsDisplayed = True
vFormattedData = rngMyRange.Value

 

[tttmack]

thanks but PrecisionAsDisplayed wont work. For example a value of 0.1234 with NumberFormat = "0.0%" has a .Text property of "12.3%". After calling PrecisionAsDisplayed = True, the .Value property is 0.123, but in order to make it look like 12.3% you would still need to either use .Text or .Value & .NumberFormat