SOLVED
Home

Public array

%3CLINGO-SUB%20id%3D%22lingo-sub-687953%22%20slang%3D%22en-US%22%3EPublic%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687953%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20confused%20about%20how%20to%20declare%20a%20public%20array.%26nbsp%3B%20I%20originally%20(before%20I%20realized%20that%20I'll%20need%20to%20reference%20it%20elsewhere)%20had%20it%20declared%20in%20a%20Sub%20in%20Module4%20as%20follows%3A%3C%2FP%3E%3CPRE%3ESub%20MyTest()%3CBR%20%2F%3E%20%20%20Dim%20Horse(5)%20As%20String%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20Horse(1)%20%3D%20%22BE%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20Horse(2)%20%3D%20%22BI%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20Horse(3)%20%3D%20%22BL%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20Horse(4)%20%3D%20%22BU%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20Horse(5)%20%3D%20%22CA%22%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3EI%20tried%20this%20which%20I%20understood%20from%20what%20I%20read%20here%20and%20elsewhere%20would%20make%20it%20available%20from%20any%20other%20Sub%20in%20the%20same%20module%3A%3C%2FP%3E%3CPRE%3EPublic%20Horse(5)%20as%20String%3C%2FPRE%3E%3CP%3EBut%26nbsp%3Bthat%20produces%20a%20compile%20error%20%22Invalid%20attribute%20in%20Sub%20or%20function.%22%3C%2FP%3E%3CP%3EI%20tried%20declaring%20the%20Sub%20public%20but%20that%20didn't%20help%3A%3C%2FP%3E%3CPRE%3EPublic%20Sub%20MyTest()%3C%2FPRE%3E%3CP%3EI%20tried%20putting%20the%20declaration%20in%20the%20Workbook_Activate()%20method%20but%20that%20produces%20a%20Type%20Mismatch%20error%20when%20the%20array%20is%20referenced%20elsewhere.%3C%2FP%3E%3CP%3EI've%20been%20reading%20many%20threads%20on%20the%20subject%20of%20declaring%20public%20arrays%20and%20I%20think%20that%20I'm%20following%20their%20advice.%26nbsp%3B%20But%20I'm%20obviously%20missing%20something%20and%20my%20head%20is%20starting%20to%20hurt.%3C%2FP%3E%3CP%3EThanks%20for%20any%20help.%3C%2FP%3E%3CP%3EPS%3A%20Is%20that%20how%20I%20should%20format%20code%20in%20a%20post%20(above)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-687953%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-688044%22%20slang%3D%22en-US%22%3ERe%3A%20Public%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-688044%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%26nbsp%3B%20try%20declaring%20the%20variable%20outside%20of%20the%20sub..%3C%2FP%3E%3CP%3Epublic%20Horse(5)%20as%20string%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20myTest()%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Horse(1)%3D%22BE%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eend%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-688332%22%20slang%3D%22en-US%22%3ERe%3A%20Public%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-688332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F338084%22%20target%3D%22_blank%22%3E%40EricBrady%3C%2FA%3E%26nbsp%3BThanks%20but%26nbsp%3Bthat%20produces%20a%20compile%20error%2C%20%22Invalid%20outside%20procedure.%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-689859%22%20slang%3D%22en-US%22%3ERe%3A%20Public%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-689859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3Ehere%20is%20a%20good%20explanation%20of%20scope%26nbsp%3B%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F2722146%2Fhow-do-i-declare-a-global-variable-in-vba%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F2722146%2Fhow-do-i-declare-a-global-variable-in-vba%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
bvelke
Occasional Contributor

I'm confused about how to declare a public array.  I originally (before I realized that I'll need to reference it elsewhere) had it declared in a Sub in Module4 as follows:

Sub MyTest()
Dim Horse(5) As String
   Horse(1) = "BE"
   Horse(2) = "BI"
   Horse(3) = "BL"
   Horse(4) = "BU"
   Horse(5) = "CA"
End Sub

I tried this which I understood from what I read here and elsewhere would make it available from any other Sub in the same module:

Public Horse(5) as String

But that produces a compile error "Invalid attribute in Sub or function."

I tried declaring the Sub public but that didn't help:

Public Sub MyTest()

I tried putting the declaration in the Workbook_Activate() method but that produces a Type Mismatch error when the array is referenced elsewhere.

I've been reading many threads on the subject of declaring public arrays and I think that I'm following their advice.  But I'm obviously missing something and my head is starting to hurt.

Thanks for any help.

PS: Is that how I should format code in a post (above)?

3 Replies

@bvelke  try declaring the variable outside of the sub..

public Horse(5) as string

 

Sub myTest()

      Horse(1)="BE"

 

end Sub

 

@EricBrady Thanks but that produces a compile error, "Invalid outside procedure."