Storing UBound value in an integer variable.

[ice00monster]

I have no idea at all why is this not working. Can someone explain? I am at a total loss at this one.

Please Login or Register  to view this content.

When I use Test1 function to input an array of cells it always chucks out a VALUE error. Say I selected B1:B4, instead of giving the result of 4 as expected, just a value error.

[MrShorty]

When I see this, I expect to see that arraytest is probably typed as a Range object variant rather than a variant containing a VBA array. Run this modification (all I did was add a stop statement)

Please Login or Register  to view this content.

When it gets to the Stop and brings up debug mode, look in the locals window or set a watch for Typename(arraytest) or add a debug.print typename(arraytest) or other strategy to see what type arraytest is.

What are you trying to get from the Ubound() function? Usually I am trying to get a count of how many cells are in the range object, so I often use val=arraytest.Count instead of UBound.

Does arraytest need to be able to handle both range object and VBA array inputs? You might need a block IF() at the top of the procedure that detects what data type arraytest is and choose the appropriate course of action.

I sometimes find it valuable to be more specific when typing input arguments. In this case, if you only ever plan to pass Excel ranges to the function, type arraytest as a Range object (arraytest as Range), which will make it more clear to you in your code that arraytest is not a VBA array.

In short, I think the problem is a data type problem -- UBound cannot tell what the upper index of an Excel range is. The final solution to the problem depends on exactly how you need the procedure to work with arraytest.

[rorya]

You're passing a range, not an array, so you need:

Please Login or Register  to view this content.

[ice00monster]

Got it from both of you. Thanks!