0

I just discovered that it's apparently not possible to declare a public array in a VBA class while it is fine to declare it private.

I am wondering if this is has a technical reason or if this is a design choice on Microsoft's part.

Either explanation doesn't make much sense to me: I cannot see a technical reason that would prevent a member to be private while it can be public as this is only an access check that is checked at runtime.

On the other hand, I don't understand why it shouldn't be possible to declare public arrays while it is perfectly fine to declare public integers or other data types.

I'd appreciate if someone could explain the rational behind all this.

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
  • [Creating VB array that is public, within class module](https://stackoverflow.com/questions/19882691/creating-vb-array-that-is-public-within-class-module) Declare as variant and then ReDim when initialize as workaround though I note you are after a reason. – QHarr Aug 27 '18 at 07:10

1 Answers1

2

I believe you'd need to ask the persons who created the Visual Basic (or maybe even Basic) programming language as to "why". It seems to be inherent to the languages. As far as VBA goes, the restriction comes from VB6, on which VBA bases. I find this reference in a Google search:

Declaring data as Public in a Form means you are creating a Property on that Form, using abbreviated syntax. A Property cannot be an array using that shortcut syntax.

To say this another way, "Public" only means "global" for old-fashioned static (BAS) modules. For everything else Public means something entirely different.

And this:

Instead of Arrays, You can use Collection object or Your own Collection Class. VB6 does not allow to declare Constants, Arrays, User Defined Types as Public.

From the VBA Help topic Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module

Not all variables in an object module can be declared as Public. However, procedures are Public by default, and Property procedures can be used to simulate variables syntactically. This error has the following causes and solutions:

Concerning arrays, specifically

You declared a Public array in an object module. Although a procedure can't return an array, it can return a Variant that contains an array. To simulate a Public array in a class module, use a set of Property procedures that accept and return a Variant containing an array.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43