Variant array passed from UserForm to Module in VBA is null/empty

Question

I have a UserForm with a ListBox for the user to select values. Those values are populated in UserForm_Initialize() via a function call to the base module, which returns an array as variant. This works without problems.

If the user selects some values and presses a button, the buttons Click event calls another function in the base module to pass on the user-entered array and compute things. This does not work at all. The value received in the base module is always nonexistent (not even null, but I don't know the correct VBA term, nothing is there at all).

Things I have tried so far:

Passing all arguments ByVal: Did not make a difference
Using global shared variables: This did work, but I don't want to rely on them if all I do is pass a single array to a single function. This also introduces state into the code which has to be managed, especially when reusing the function
Accessing the functions by full qualifiers: Did not make a difference. The functions are found and executed correctly, but the argument variables are empty, therefore the functions fail later on when doing the calculations.

My question is: How can I pass arrays from UserForms to Modules (not vice versa) without relying on global variables and without losing the array content?

This question may be related to this question about passing a String from Form to Module, but the accepted answer does not help in my case (using global variables).

You can see my answer (passing argument array from userform to a module with 2 methods (function or sub) here stackoverflow.com/questions/35211641/… — Patrick Lepelletier
– Patrick Lepelletier, Commented Feb 16, 2016 at 20:37

user121391 · Accepted Answer · 2016-02-16 17:06:35Z

0

When adding the code as requested in the comments, I stumbled upon that fact that I could print the content of the array, but it would not show anything in the debugger and the size would be 0.

The size issue was because I used Len(array) instead of Application.CountA(array) and I had a leftover On error resume next from earlier still in the code, which meant that no error was raised and size was always set to zero... This was the reason for the strange behaviour.

answered Feb 16, 2016 at 17:06

user121391

6376 silver badges18 bronze badges

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

1 Comment

Patrick Lepelletier Over a year ago

len(join(array)) would work i think. If the array is not 0 size, ubound(array) works too.

Collectives™ on Stack Overflow

Variant array passed from UserForm to Module in VBA is null/empty

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related