PDA

View Full Version : [SOLVED] Access array between different modules



sindrefm
12-18-2014, 02:26 AM
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
12-18-2014, 02:33 AM
The declaration has to reside in a macromodule:


Public sn

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

sindrefm
12-18-2014, 03:22 AM
I did not understand that. Can you describe it further?

GTO
12-18-2014, 03:54 AM
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
12-18-2014, 04:36 AM
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
12-18-2014, 05:16 AM
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
12-18-2014, 05:47 AM
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
12-18-2014, 06:13 AM
Thanks SamT, I will try that.

sindrefm
12-18-2014, 06:26 AM
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
12-18-2014, 06:45 AM
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
12-18-2014, 06:55 AM
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
12-18-2014, 08:20 AM
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
12-18-2014, 11:20 AM
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
12-18-2014, 03:17 PM
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
12-18-2014, 08:27 PM
@ Mike,

Thank you. My meds are off or something.

Paul_Hossler
12-20-2014, 09:48 AM
@Mike and @SamT --


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


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
12-22-2014, 12:14 AM
Thanks for the help.

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

Sub Check()
Call SC_TempA

....code

End Sub