Global Variable Empty Value when used in Array

[ShieBoon]

Hi all,

I have a global variable, FinalRow, which calculates the finalrow of the records in the Worksheet_Activate event.

The global variable is declared in a module.

I created a button and assigned a macro (below) to it to allow users to view the records. I used an array in it.

Sub View_All_Records_Form()
Dim ArrayOfAllRecords As Variant
ArrayOfAllRecords = Worksheets("MasterList").Range("A3:AG" & FinalRow)
Load frm_ViewAllRecords
With frm_ViewAllRecords
.txt_VARDealType = ArrayOfAllRecords(1, 1)
End With
frm_ViewAllRecords.Show
End Sub

I used FinalRow, the global variable, for the filling of data in the array. frm_ViewAllRecords is the userform.

Now when i press the button, the userform loads. But after i close it by pressing the red X button at the top right hand corner of the window, and press the button once more, an error is generated.

Run time error 1004
"Application-defined or object-defined error"

I press debug and the following row is highlighted.

ArrayOfAllRecords = Worksheets("MasterList").Range("A3:AG" & FinalRow)

When i hover my mouse over to 'FinalRow', the value is 0.

Please advice me.
I'm using excel 2003

Thank you
Shie Boon

 

[ShieBoon]

Hi all, i have solved the problem by placing the data in a named range, and filling the array with the named range. But still, i am curious why this has happened. Anyone has any ideas?

 

[Ruddles]

Where did you declare FinalRow? Show us the statement?

 

[ShieBoon]

Hi Ruddles,

I declared FinalRow in a module, before all procedures.

Public FinalRow As Integer

Thanks

 

[Ruddles]

Weird... no, I can't work it out - that looks fine.

Oh well, as long as your code is working!

 

[xenou]

Nor I.

But two notes:

Public FinalRow As Integer

Variables that are used to hold rows (in VBA) should be Longs, not Integers, since row numbers can be greater than ~32,000.

In any case, now you know why public variables are discouraged. There's various reasons why they can be lost.

Better would be a function in a standard module that returns the last row - then call that function at the time you need the row. Even better, a named range or named formula - as you worked out already.

 

[ShieBoon]

Hi Ruddles and xenou,

To Ruddles, haha yes yes.

To xenou, I will note those two.

Thank you both for the feedback! Appreciate it! =) Have a great day ahead!

 

[tusharm]

I've seen similar problems with global variables and closing a userform with the X button. If I recall correctly, I solved the problem by trapping the userform close (use the QueryClose event procedure) and hide the form...or maybe I unloaded the form. One or the other. Try each and see what happens.

Hi all,

I have a global variable, FinalRow, which calculates the finalrow of the records in the Worksheet_Activate event.

The global variable is declared in a module.

I created a button and assigned a macro (below) to it to allow users to view the records. I used an array in it.

Sub View_All_Records_Form()
Dim ArrayOfAllRecords As Variant
ArrayOfAllRecords = Worksheets("MasterList").Range("A3:AG" & FinalRow)
Load frm_ViewAllRecords
With frm_ViewAllRecords
.txt_VARDealType = ArrayOfAllRecords(1, 1)
End With
frm_ViewAllRecords.Show
End Sub

I used FinalRow, the global variable, for the filling of data in the array. frm_ViewAllRecords is the userform.

Now when i press the button, the userform loads. But after i close it by pressing the red X button at the top right hand corner of the window, and press the button once more, an error is generated.

Run time error 1004
"Application-defined or object-defined error"

I press debug and the following row is highlighted.

ArrayOfAllRecords = Worksheets("MasterList").Range("A3:AG" & FinalRow)

When i hover my mouse over to 'FinalRow', the value is 0.

Please advice me.
I'm using excel 2003

Thank you
Shie Boon

 

[ShieBoon]

Hi tusharm,

Thanks for the idea. When i changed the variable type of FinalRow during runtime, the value is lost too.