Range.Value and Range.Value2 in VBA not working with new Geography and Stocks data types for some columns

[ttt123]

When using VBA, if I want to get a table of cell values, I usually use Range.Value or Range.Value2 over the entire table range and get a 2d-array as a result. This is much, much faster than getting each cell in the table one at a time using Range.Value/Range.Value2.

However, I noticed with the new Geography and Stocks data types I cannot reliably get values using Range.Value or Range.Value2 for certain fields. Cells that contain the little card icon on the left side of the cell do not seem to work with Range.Value/Range.Value2 and instead return "Error 2015" in VBA. Range.Text returns the correct value, but it has the disadvantage of not being vectorized and can only work with a single cell at a time.

I am looking to see if anyone knows a way to retreive the correct values in VBA in a vectorized manner where I do not have to iterate through each individual cell. Currently, the best approach that I can come up with is to use Range.Value2 and then for each position where there is an error, fill it in with Range.Text. An alternate solution would be to copy the range to the clipboard then PasteSpecial->values in a temp worksheet/workbook and then get Range.Value2 for the pasted values followed by deleting the temp worksheet/workbook. Both of these methods are slow and I'd also prefer to avoid using the clipboard since it will overwrite exisitng clipboard data. Does anyone have any suggestions as to a better way to get these values in a vectorized manner?

For example, look at the following spreadsheet which uses some VBA functions for testing.

Function RangeText(rng As Range) As String
    RangeText = rng.Text
End Function
Function RangeValue(rng As Range) As String
    RangeValue = rng.Value
End Function
Function RangeValue2(rng As Range) As String
    RangeValue2 = rng.Value2
End Function
Function RangeFormula(rng As Range) As String
    RangeFormula = rng.Formula
End Function
Function RangeFormula2(rng As Range) As String
    RangeFormula2 = rng.Formula2
End Function

Sample spreadsheet with ShowFormulas disabled:

Sample spreadsheet with ShowFormulas enabled: