Can ByRef be used across modules?

[JenniferMurphy]

I have a Sub (ReOrder) that is currently called from an .xlsm workbook by a command button control. It's job is to reorder the values in the cells in a named range.

I'd like to move that code to an add-in module so it can be used by other workbooks and by other VBA code. My plan is to move the actual reordering code to the add-in module and change the ReOrder Sub to read the range of values into an array and call the add-in code to do the reordering.

I have a couple of questions:

1. Should the add-in code be another Sub or a UDF?
2. Should the calling code (ReOrder sub or some other code) pass the array as (a) ByVal and return the reordered array, (b) ByRef, or (c) make it a global variable that the add-in code can access?

My inclination is to make the add-in code a Sub and pass the array ByRef so it can be reordered in place. That way any Subs called liek ReOrder is, can read the values into an array, call the add-in code to do teh reordering, and then wrote that back to the sheet. And some other VBA code, such as a function that is passed a list to reorder, can call that same add-in sub in trhe same way.

I'd appreciate any comments or suggestions.

Thanks

 

[RoryA]

1. I'd suggest you make it a function and return the sorted array.
2. Arrays declared as such are actually always passed ByRef. You cannot declare a procedure to take an array argument ByVal. You can, of course, just use a Variant instead.

 

[JenniferMurphy]

1. I'd suggest you make it a function and return the sorted array.
2. Arrays declared as such are actually always passed ByRef. You cannot declare a procedure to take an array argument ByVal.

This website seems to say otherwise, at least with regard to "structures":

Passing Arguments by Value and by Reference (Visual Basic)

About 1 screen down under "Choice of Passing Mechanism", it says:

I've never used a VBA structure. Apparently, it can be passed ByRef while an array cannot. Curious. Is there a logical reason for this or just another m$ft idiotsyncrasy?

You can, of course, just use a Variant instead.

Are arguments of type Variant handled differently than other types? Is that what they mean above by "value type" -- ie, not type Variant?

I was planning on declaring the array as Variant, anyway. The arrays I am working with now are all integers, but since I am making this a general utility function in an add-in, maybe they could be something else in the future. But maybe I should just stick with integer arrays for now and worry about a more general solution if that ever comes up.

Why are Variants handled differently? Is it because they can contain complex structures (arrays of arrays)? But the page above says that structures can be passed ByRef. I don't think I will ever figure all this out. (sigh)

If I decide that the code will only deal with integers, would you pass an Integer (or Long) array or a Variant?

Thanks

 

[RoryA]

This website seems to say otherwise, at least with regard to "structures"

Since structures and arrays aren't the same thing, I don't see the relevance of that link?

Apparently, it can be passed ByRef while an array cannot

I think you've confused ByRef and ByVal there.

Variants are slightly odd since they can contain anything (which is why they take up more memory than normal data types) - for details on the storage of the various data types, this is a good overview: How Visual Basic 6 Stores Data | CodeGuru