Declaring Constants in an Array

Question

I wish to declare a 'fixed' range of co-ordinates. Something like ...

Public arrCoord1 as Integer

Public arrCoord2 as Integer

arrCoord1 = Array(500, 600, 50, 100)

arrCoord2 = Array(600, 700, 60, 110)

There are multiple sets of co-ordinates that are accessed frequently across several procedures.

They may change, but only if the design were to change. They need to be accessible and documented within a group and not scattered through the project. That's why I want to declare them once at the top of the code.

The above code does not compile.

Should they be Contstants?

Do they need to be Public? 

Whats the best approach and coding solution?

Answer 1

Re:  "I tried the Variant declaration before I changed it to Integer. Neither worked!"

You need to declare values that are fixed as Constants.

The syntax in a class module is:

   Private Const lngVariable As Long = 999

The syntax in a standard module is: 

   Public Const lngVariable As Long = 999

Unfortunately, VBA will not accept an array as a constant.

Also, class modules are private modules, so anything in them is not available to other modules unless the module name is specified and that only works when the variable is declared as public.

Move your declarations to a standard module...

  Public arrCoord1 as Variant

  Public arrCoord2 as Variant

Then in your UserForm Initialize event or in a Sub that is called when the workbook opens, you can initialize the arrays...

Private Sub UserForm_Initialize()

  arrCoord1 = Array(500, 600, 50, 100)

  arrCoord2 = Array(600, 700, 60, 110)

End Sub

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

Use Variant arrays as follows:

Public arrCoord1 As Variant

Public arrCoord2 As Variant

Sub MAIN()

Call Initializee

Call followup

End Sub

Sub Initializee()

arrCoord1 = Array(500, 600, 50, 100)

arrCoord2 = Array(600, 700, 60, 110)

End Sub

Sub followup()

MsgBox arrCoord1(0)

End Sub

The values will be available to all subs in the module

Answer 3

If I use .....

Dim arrCoord1 as Variant

Dim arrCoord2 as Variant

arrCoord1 = Array(500, 600, 50, 100)

arrCoord2 = Array(600, 700, 60, 110)

.... in every procedure; it compliles OK!

I just want to remove the duplication and place the code in one (public ??) place.

Answer 4

I am running this as part of a UserForm.

'Compile Error: Invalid outside procedure' 

I tried the Variant declaration before I changed it to Integer. Neither worked!

Answer 5

Martyn,

Re:  "The above code does not compile."

Because an Integer cannot contain an Array.

Change the declaration to Variant.

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)