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"
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?
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
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
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
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
@ 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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.