• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using the contents of an array in other procedures

H

Harry Covair

Member
Hi the forum,
In the Workbook_Open event in ThisWorkbook, I fill an array defined as “Public arrmsg() as variant”. A debug.print confirms that the variable “arrmsg” contains the information.
Problem.
When I want to use an element of this array in a procedure belonging to another module (not in ThisWorkbook), a message "Subscript out of range" seems to indicate that the content of the table is empty.
The question is: is it possible to keep the contents of an array to use it in other procedures?
Any ideas or suggestion would be appreciated.
Have a nice day
Harry
 
Hi Narayan,
Thanks for your answer.
Yes I did.
As suggested in the two references, the array is declared as public in a separated module in which all the variable used in several procedure are declared. Another way to ask the question is: what should VBA forces to initialize a public variable?
Regards
Harry
 
Hi Harry ,

You can do the Initialization in the Workbook_Open procedure , checking for IsEmpty(variable) ; if there has not been any assignment , the test will return True ; once any value , even a null value has been assigned to the variable , the test will return False.

Narayan
 
Hi Narayan
Apologies,
I fear that my last question was poorly worded.
To explain the public array variable becomes empty in other procedures, there is certainly a reason to VBA act so. That is what I try to understand.
Regards
Harry
 
Hi Harry ,

I am sorry , but I am not able to understand your question. Any examples or code to illustrate your problem ?

Narayan
 
Hi Narayan,
Some explanation describing the context.
In workbook_open I populate the public array variable arrmsg; (wksh is the worksheet containing the text in the 27th column). I remember that the variable is declared in a separated module.
Sub workbook_open()
…………………some code………………………..
For i = 0 To LastRow
arrmsg(i) = wksh.Cells(i + 2, 27).Value
Next i
…………………some code………………………..
End sub
While I stay in the workbook_open event procedure, I can use the contents of the arrmsg variable without any problem.
But when in another procedure belonging to another module, I want to use an item of the array variable. For example:
Sub Mainproc()
…………………some code………………………..
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = msg_title & " - !!! " & arrmsg(49) & "!!!"
…………………some code………………………..
End sub
On the instruction in red, VBA generate an error saying that the 49th (or any other) item of the variable arrmsg doesn’t exist (Subscript out of range).
Do my explanation allows a better understanding of the problem?
Regards

Harry
 
Hi Harry ,

1. Is arrmsg declared as a Variant , or as an array ? If so , is it declared as a variable array ( with variable number of elements ) ? Are you using Redim to redimension the array ?

2. Have you checked the value of any element of arrmsg or UBound(arrmsg) during and after execution of the Workbook_Open procedure ? Is the variable being reset in any other procedure ?

Can you upload your file ?

Narayan
 
Hi Narayan,
I found the rub.
In the workbook_open procedure I create at each run some command buttons.
I found by chance: http://support.microsoft.com/kb/231089
and I concluded that adding a button will force the sheet to go in design mode and that is the reason why the variables created after the buttons are reset.
I'm really sorry but I didn't know this reaction of VBA.
In any case thank you for the interest you have shown in my problem.
Kind regards

Harry
 
With your description, probably you need a global variable. See if the following works for you.
e.g.

In an inserted[standard] module:
Code: 
Global glbvarHarrySArray As Variant
PS: Use this module for declaring variables only

In ThisWorkbook module:
Code: 
Private Sub Workbook_Open()
If IsEmpty(glbvarHarrySArray) Then
    glbvarHarrySArray = Array("Harry", "Narayan", "Shrivallabha")
End If
Call Sheet1.ReuseArray
End Sub

In Sheet1 module:
Code: 
Public Sub ReuseArray()
For i = LBound(glbvarHarrySArray) To UBound(glbvarHarrySArray)
    Debug.Print glbvarHarrySArray(i)
Next i
End Sub
 
Shri, I wouldn't recommend using the keyword Global to declare a public variable. It was used in earlier versions of VB (2/3 and probably even before), and is now provided for backward compatibility (starting VB4). Global and Public declarations work the same way, except that one is not allowed to use the Global keyword within a Class module (UserForm, Workbook, Sheet etc).

This may be a good read > http://www.tek-tips.com/faqs.cfm?fid=5319
 
Hello Sam Mathai Chacko,
thanks for the reference. In fact I didn't know that a global delaration existed for procedure. I use usually a Public declaration.

Hello Shrivallabha,
Thanks for your answer.
I tested the code as proposed.
After adding a I as integer, everything is OK and the immediate window displays the correct results.
I also made some changes to reproduce the environment that posed a problem.
In the Workbook_Open procedure, I added some code to create a button.
The sub CommandButton1_Click procedure is in the module sheet 1. See here an overview.
Workbook_Open

Code: 
Private Sub Workbook_Open()
If IsEmpty(glbvarHarrySArray) Then
    glbvarHarrySArray = Array("Harry", "Narayan", "Shrivallabha")
End If
[I]    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _[/I]
[I]        , DisplayAsIcon:=False, Left:=222.75, Top:=60.75, Width:=144.75, _[/I]
[I]        Height:=44.25).Select[/I]
[S]'Call Sheet1.ReuseArray[/S]
End Sub

Module1

Code: 
Global glbvarHarrySArray As Variant
Public i As Integer

Module Sheet1

Code: 
Public Sub ReuseArray()
For i = LBound(glbvarHarrySArray) To UBound(glbvarHarrySArray) ' the problem is here
    Debug.Print glbvarHarrySArray(i)
Next i
End Sub
 
Private Sub CommandButton1_Click()
Call Sheet1.ReuseArray
Call ReuseArray
End Sub


On the statement marked "the problem is here", VBA gives an error message: "Type mismatch." Which may tend to prove that the variable glbvarHarrySArray is empty.
In my case, I solved the problem by incorporating buttons in the sheet and using the visible property.
Kind regards

Harry
PS How can you select the VB code option in the Code window?
 
Not stepping on anybody's shoe, but there's no Orange text... you'll need to edit your post and correct the format (suggest removing the entire code text, and replacing it fresh)
 
You must log in or register to reply here.
Back
Top