How to create a global array in VBA?

Question

I am trying to create a global array by converting a table into an array in the first code, and then calling it in the second code. However, i get a run-time error '13' type mismatch when calling my second code.

Public prio_row_num
Public prioritytbl As Variant

Sub Tabletoarray()

Dim prioritytbl As Variant

Worksheets("Prod_met").Activate

prioritytbl = ActiveSheet.ListObjects("prioritylist").DataBodyRange.Value

End Sub

Sub test()

MsgBox (prioritytbl(2, 2))

End Sub

I want to be able to call the prioritytbl in subsequent parts of my code.

score 0 · Accepted Answer · answered May 07 '19 at 10:58

You shouldn't use Global variables if possible. Let's give you an example on how could you do it:

Option Explicit
Sub Tabletoarray()

    Dim prioritytbl As Variant
    prioritytbl = Worksheets("Prod_met").ListObjects("prioritylist").DataBodyRange.Value
    Call test(prioritytbl)

End Sub
Sub test(prioritytbl As Variant)

    MsgBox (prioritytbl(2, 2))

End Sub

Said this, if you need to use the global variable, you can't declare it again in any sub:

Option Explicit
Public prio_row_num
Public prioritytbl As Variant

Sub Tabletoarray()

' Dim prioritytbl As Variant this needs to be deleted

    prioritytbl = Worksheets("Prod_met").ListObjects("prioritylist").DataBodyRange.Value

End Sub
Sub test()

    MsgBox (prioritytbl(2, 2))

End Sub

I would also recommend you to read on how to avoid using .select or .activate

How to create a global array in VBA?

1 Answers1