Access array between different modules

[sindrefm]

Hi,

I want to set up an array of different strings in one module, and access the array from another module.
I tried something here below, but I can not access from another module.
Can anyone help me on the way? Thanks.

Public arrSC_TempA(1 To 10) As String

arrSC_TempA(1) = "A"
arrSC_TempA(2) = "B"
arrSC_TempA(3) = "C"
arrSC_TempA(4) = "D"
arrSC_TempA(5) = "E"
arrSC_TempA(6) = "F"
arrSC_TempA(7) = "G"
arrSC_TempA(8) = "H"

[snb]

The declaration has to reside in a macromodule:

Public sn

Sub M_snb()
  sn = [transpose(char(row(65:72)))]
End sub

[sindrefm]

I did not understand that. Can you describe it further?

[GTO]

Greetings sindrefm,

You can declare a variable above any procedures (a Sub or a Function procedure) in a Module, but you need to populate the variable within a procedure. Procedures are what "run" or "Execute", does that make sense?

By silly example:

Option Explicit
    
Public arrSC_TempA(1 To 10) As String
    
Sub example()
  
  arrSC_TempA(1) = "A"
  arrSC_TempA(2) = "B"
  arrSC_TempA(3) = "C"
  arrSC_TempA(4) = "D"
  arrSC_TempA(5) = "E"
  arrSC_TempA(6) = "F"
  arrSC_TempA(7) = "G"
  arrSC_TempA(8) = "H"
  
  MakeWords
  
End Sub
  
Sub MakeWords()
Dim strText As String
Dim n       As Long
  
  strText = "I took a "
  
  For n = 0 To 2
    strText = strText & arrSC_TempA(Array(3, 1, 2)(n))
  Next
  
  MsgBox strText & "."
  
End Sub

Mark

[sindrefm]

Yes, that makes sense. Thank you.

I wanna use all the array elements as a criteria in an IF sentence. Is it an easy way to do that?

I have tried the following, but I get error message.

If .Cells(i, ColA).Value = arrSC_TempA(1 And 2 And 3 ......) Then
.........
End If

If .Cells(i, ColA).Value = arrSC_TempA(1, 2, 3 ......) Then
.........
End If

[SamT]

Public arrSC_TempA(8) As String

Sub Initialize_arrSC_TempA()
  arrSC_TempA(0) = "Zed"
  arrSC_TempA(1) = "Able"
  arrSC_TempA(2) = "Baker"
  arrSC_TempA(3) = "Cat"
  arrSC_TempA(4) = "Dog"
  arrSC_TempA(5) = "Eel"
  arrSC_TempA(6) = "Frog"
  arrSC_TempA(7) = "Golf"
  arrSC_TempA(8) = "Hotel"
End Sub

For n = 0 to 8
   'Compare this cell to every value in Array
   If .Cells( i, 1).Value = arrSC_TempA(n)  Then 
      'Do something here
   End If
Next n

[sindrefm]

I have set it up like this:

For n = 0 To 2
strSC_TempA = arrSC_TempA(Array(1, 2, 3, 4, 5, 6, 7, 8)(n))
Next

But it does not work as what I expect.

Can you explain how that code works?

[sindrefm]

Thanks SamT, I will try that.

[sindrefm]

I want that If .Cells(i, 1).Value is different from all the variables in the array THEN I want to proceed.
Do you know a way to do that? I cannot get this code to do that:


For n = 0 to 8
'Compare this cell to every value in Array
If .Cells( i, 1).Value <> arrSC_TempA(n) Then
'Do something here
End If
Next n

[Paul_Hossler]

I think I understand -- this uses a Boolean function to see if a value is in the array or not.

True = it's in the array, and False = it's not

Option Explicit
Public arrSC_TempA(1 To 10) As String
Sub drv()
    Call demo_init
    
    MsgBox InArray(arrSC_TempA, "E")
    MsgBox InArray(arrSC_TempA, "Z")
    
    MsgBox InArray(arrSC_TempA, "A")
    MsgBox InArray(arrSC_TempA, "J")
End Sub

Sub demo_init()
 arrSC_TempA(1) = "A"
 arrSC_TempA(2) = "B"
 arrSC_TempA(3) = "C"
 arrSC_TempA(4) = "D"
 arrSC_TempA(5) = "E"
 arrSC_TempA(6) = "F"
 arrSC_TempA(7) = "G"
 arrSC_TempA(8) = "H"
 arrSC_TempA(9) = "I"
 arrSC_TempA(10) = "J"
End Sub

Function InArray(A As Variant, V As Variant) As Boolean
    On Error Resume Next
    InArray = Not IsError(Application.WorksheetFunction.Match(V, A, 0))
    On Error GoTo 0
End Function

[SamT]

Dim TheCellValueIsTheSameAsOneArrayValue As Boolean

TheCellValueIsTheSameAsOneArrayValue = False

For n = 0 to 8
       'Compare this cell to every value in Array
       If .Cells( i, 1).Value = arrSC_TempA(n)  Then 
             TheCellValueIsTheSameAsOneArrayValue = True
       End If
Next n

If Not TheCellValueIsTheSameAsOneArrayValue Then
'   Do something here
End If

Good bye.

Good luck.

[sindrefm]

I will explain it more correctly. I want this in a more easier way:


If Not .Cells( i, 1).Value = arrSC_TempA(1) And Not .Cells( i, 1).Value = arrSC_TempA(2) And Not ............ Then
'Do something here
End If

Instead of checking every array variable with AND NOT, I want a more easier way to do it if possible. Because the line of code will be very long.
Thanks

[mikerickson]

Two thoughts.
To See if a value is in an array, the Worksheet function Match can be used.

If IsError(Application.Match(.Cells(I, 1), arrSC_TempA, 0)) Then
    MsgBox "cell is not in array"
Else
    MsgBox "cell is in array")
End IF

Other thought: arrSC_TempA might be cast as a function rather than a Public variable.

Function arrSC_TempA() As Variant
    Dim Result(1 to 10) As String
    Result(1) = "A"
    Result(2) = "B"
    ' etc

    arrSC_TempA = Result
End Function

[snb]

Instead of:

If Not .Cells( i, 1).Value = arrSC_TempA(1) And Not .Cells( i, 1).Value = arrSC_TempA(2) And Not ............ Then
'Do something here
End If

if instr("|aa|bb|cc|dd|ee|,"|" & .cells(j,1) & "|")=0 then ......
 

@Mike

If IsArray(c00) then 
 '  your code
else
 'your code
end if

[SamT]

@ Mike,

Thank you. My meds are off or something.

[Paul_Hossler]

@Mike and @SamT --

[OPINION]
I've always been leery of having functions or subs reference or modify directly a variable outside of their immediate scope.
[/OPINION]

That's why when I used Match() inside the Boolean-returning function in #10, I passed the array and the value to Match()-against. The side benefit was that there was no VBA looping involved and the intrinsic Match() was used.

It is also flexible and you can pass a range to it

Sub drv()
    MsgBox InArray(ActiveSheet.Range("A:A"), "zz")
End Sub

[sindrefm]

Thanks for the help.

It looks like when I "call" the array sub it works.

Sub Check()
Call SC_TempA

....code

End Sub