WorksheetFunction functions change data types

Question

This post is intended to share the undocumented product feature for future use as per this blog post.

I found an unexpected behavior of some Excel's functions when they are called from VBA. They change some data types:

Sub test1()
  Dim ou, tu, soa, j&: ReDim ou(1 To 2, 1 To 2), soa(1 To 2, 1 To 1)
  For j = 1 To 2
    ou(j, 1) = j: ou(j, 2) = Now(): soa(j, 1) = j
  Next
  With Application
    Debug.Print "Expected:", TypeName(ou(1, 1)), TypeName(ou(1, 2))
    tu = .Sort(ou, 2)
    Debug.Print "Sort:", TypeName(tu(1, 1)), TypeName(tu(1, 2))
    tu = .XLookup(1, soa, ou)
    Debug.Print "Xlookup:", TypeName(tu(1)), TypeName(tu(2))
    tu = .VLookup(1, ou, 2, False)
    Debug.Print "Vlookup:", , TypeName(tu)
  End With
End Sub

-->

Expected:     Long          Date
Sort:         Double        String
Xlookup:      Double        String
Vlookup:                    String

So, it changes Long to Double and Date to String. Be aware of it!

Considering this bug as a feature, how can we suppress it's effect?

UPD

Additional functions falling into this feature: IfError, IfNa, Lookup, HLookup, Filter, Choose. It looks like all functions expected to return argument values unchanged falling into this feature.

Yes, excel and vba do a bunch of implicit data type conversions. If maintaining the data types is important, you can either reset them after sorting, sort a worksheet range, or use a custom VBA sort routine (eg. bubblesort, quicksort, etc) where you have more control. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 20 at 13:13
@RonRosenfeld Truly, I tested a couple functions more - same results. — rotabor
– rotabor, Commented Jul 20 at 18:34
To answer your last edit (question), you cannot suppress this behavior. You can merely work-around it using one of the options presented in the comments and/or answer. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 21 at 17:32

rotabor · Accepted Answer · 2025-07-21 15:29:15Z

0

If Long to Double conversion does not affect so much, CDate might be necessary to revert String back to Date:

  r = CDate(ou(1, 2)) + 1 / 3 ' add 8 hrs. to the timestamp

Alternatively, use Double instead of Date since the Date is natively Double, indeed:

  For j = 1 To 2
    ou(j, 1) = j: ou(j, 2) = CDbl(Now())
  Next

edited Jul 21 at 15:29

answered Jul 20 at 10:10

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

WorksheetFunction functions change data types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related