Access VBA written inside form module, need to access Array from there in sub in a Module 2

[03Explorer]

Using MS Access 365, I have an array established in a standard FORM subroutine.

I am trying to use a MODULE Sub that utilizes the established Array.

I have tried changing the type of FORM SUB to Public. No luck. I also tried changing the DIM statement in the FORM SUB to Public... again, no luck. I have the new Module SUBroutine set to Public... again, no luck.

What am I missing?

 

[Andrzejek]

Just an example:

Option Explicit

Public aryMyArray(5) As String

Public Sub MyPublicSub()

aryMyArray(0) = "Hello"
aryMyArray(1) = "User"
aryMyArray(2) = "Explorer"
aryMyArray(3) = "from"
aryMyArray(4) = "TT"

End Sub

Option Explicit

Private Sub cmdOK_Click()
Dim i As Integer

Call MyPublicSub()

For i = LBound(aryMyArray) To UBound(aryMyArray)
    Debug.Print aryMyArray(I)
Next i

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

So Andy, you are building and populating the array in a Module Sub; then calling it from the Form Sub?

Any way to go other way around? Form Sub --> Module Sub?

 

[Andrzejek]

It would probably help if you would describe what you want to do in plain English, no code.

or this example:

Option Explicit

Public Sub ABCD()

Call Form1.DoSomthing("Do what you want")

End Sub

Option Explicit

Public Sub DoSomthing(ByRef strTemp As String)

MsgBox strTemp

End Sub

Private Sub cmdOK_Click()

Call ABCD

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

[Code FORM:Sub]
Public Sub bthYes_Click()
Dim AssociateArray(8, 13)
AssociateArray(1,0) = "something"
AssociateArray(1,1) = "something different"
AssociateArray(1,2) = "different"
...
AssociateArray(1,12) = "last one"

Call ExcelBehavorial

[/Code]

[Code Module2:Sub]
Public Sub ExcelBehavorial()
For i = 0 To 12
Debug.Print "Associate: " & AssociateArray(1, i)
Next
End Sub
[/Code]

 

[Andrzejek]

Well, try this:

Option Explicit

Private Sub bthYes_Click()
Dim AssociateArray(8, 13) As String

AssociateArray(1, 0) = "something"
AssociateArray(1, 1) = "something different"
AssociateArray(1, 2) = "different"
'...
AssociateArray(1, 12) = "last one"

Call ExcelBehavorial(AssociateArray)
    
End Sub

Option Explicit

Public Sub ExcelBehavorial(ByRef ary() As String)
Dim i As Integer

For i = LBound(ary) To UBound(ary)
    Debug.Print "Associate: " & ary(1, i)
Next
    
End Sub

---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

That didn't work... errors out with

[Code Compile_Error:]Type mismatch: array or user-defined type expected[/code]

 

[Andrzejek]

On which line of code?


---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

on the Form:Sub (calling the Module:Sub)

Call ExcelBehavorial(AssociateArray)

 

[03Explorer]

Andy, Am looking at this thread as a guide while chatting with you...

https://stackoverflow.com/questions/7693402/public-array-to-shuffle-values-between-subroutines

However, I haven't found the secret key to make it happen (Successfully)

Thanks for your help!

 

[combo]

Can you simply declae array in the module??

Public Sub bthYes_Click()
    AssociateArray(1,0) = "something"
    AssociateArray(1,1) = "something different"
    AssociateArray(1,2) = "different"
    ...
    AssociateArray(1,12) = "last one"

    Call ExcelBehavorial

and:

Public AssociateArray(8, 13) 
Public Sub ExcelBehavorial()
    For i = 0 To 12
      Debug.Print "Associate: " & AssociateArray(1, i)
    Next
End Sub

BTW, for me Andy's code works perfectly.

combo

 

[03Explorer]

Thanks Combo, The call (without an argument/array name) got me past that point. I replicated your code for Module2:sub like this:

[CODE Module2:sub]Public AssociateArray(8, 13)
Public Sub ExcelBehavorial()
Dim i As Integer
For i = 0 To 12
Debug.Print "Associate: " & AssociateArray(1, i)
Next
End Sub[/code]

System errors on the line <debug.print. "Run-time error 13: Type mismatch

 

[03Explorer]

Okay I am working on the point that when I am defining the Array (as I noted above in my original code share) I didn't identify the array "AS STRING"

by adding that, I found some code that needs to be updated to capture the null values.

Once I have updated my code, I'll post on the status. Thanks guys!!

 

[03Explorer]

BINGO!!! Andy's code did work... I needed to define the Array "AS STRING".

Thanks guys!! LOVE IT!!

Private Sub bthYes_Click()
Dim AssociateArray(8, 13) As String

AssociateArray(1, 0) = "something"
AssociateArray(1, 1) = "something different"
AssociateArray(1, 2) = "different"
'...
AssociateArray(1, 12) = "last one"

Call ExcelBehavorial(AssociateArray)
    
End Sub

Public Sub ExcelBehavorial(ByRef ary() As String)
    Dim i As Integer
    
    For i = LBound(ary) To UBound(ary)
      Debug.Print "Associate: " & ary(1, i)
    Next
    
End Sub