Global Variable Empty Value when used in Array

ShieBoon

Board Regular
Joined
May 3, 2011
Messages
111
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

Board Regular
Joined
May 3, 2011
Messages
111
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

Well-known Member
Joined
Aug 24, 2010
Messages
5,773
Where did you declare FinalRow? Show us the statement?
 

ShieBoon

Board Regular
Joined
May 3, 2011
Messages
111
Hi Ruddles,

I declared FinalRow in a module, before all procedures.

Public FinalRow As Integer

Thanks
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,773
Weird... no, I can't work it out - that looks fine. :confused:

Oh well, as long as your code is working!
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,583
Office Version
2013
Platform
Windows
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

Board Regular
Joined
May 3, 2011
Messages
111
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
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

Board Regular
Joined
May 3, 2011
Messages
111
Hi tusharm,

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

Forum statistics

Threads
1,085,432
Messages
5,383,634
Members
401,843
Latest member
stevensmith1

Some videos you may like

This Week's Hot Topics

Top