Setting a global variable and referring to it from a private sub routine?

Hot Topic

How do you create a flashing cell
key in date and filter the data in a week
empty datagrid
Fill word document with fields from Access form programmatically
Export excel sheet as text file with macro code
Size Form to fit user resolution
Trouble installing BSM Reporting Service
personalized command bar disappears when minimizing excel
Microsoft Ecosystem
worksheet_change and data validation

Microsoft ISV

Brightness control on Windows XP Profession 32 bit version
Avoid message boxes during macro?
ado recordset problem
break text lines in msgbox a meassage
VBA registry interface
Log in with Active X
[Excel] How to convert Excel to jpg, gif image?
Run Time Error 9 when Calling a Userform
Sum of hours using select statement & VBA
How Can I Do This With Outlook 2000 Calendar?

Setting a global variable and referring to it from a private sub routine?

Hi,

I am basically wanting to improve my code and make it more user friendly and error proof! Ok, so currently its based around a single user form which toggles on and off from a set command buttons which allows the user to view the from and select certain buttons which automate a lot of the tasks necessary from within the workbook.

I have a different amount of records/rows in it each month and I display a pop up msg box asking the user to input the number of employees to be processed each time they click a button. This is grand and works fine but is not very user friendly and if they put in too many it messes up the data! So I got some feedback from these forums a while back saying that I should redo it and instead of asking for the value in the msg box, I should automatically store it in a global variable and refer to it that way.

That's great but I'm a bit stumped on how to go about this I had the following code placed at the start of my user form but didn't know what to put it in i.e. private sub routine etc:

<code>

Public gbl_counter As Integer

gbl_counter = 135 ' this is to test if it works

</code>

Then further down in the code I refer to it like: z = gbl_counter

My problem is where do I put this i.e. userform, worksheet etc, is it correct syntax and finally, how can I get it to find out how many rows of data there are in the sheet I'm guessing that I should use some kind of loop to check if the row has data and increment a counter or something like that

Any help is greatly appreciated.


Thanks,
Scott

Answer this question

Setting a global variable and referring to it from a private sub routine?

  • Bhavitavya B

    Thanks a mill Derek!  Understand the code but not sure if im doing it exactly as you have described!  But its working and thats the main thing.

    I can't however get the following code to work

    Range("B4").End(xlDown).Count

    I have a something like: z = Range("B4").End(xlDown).Count
    It keeps bringing up z as equal to 1 when there are 134 rows of data with no blank rows until after row 134

    I also haven't done anything with the line of code:  'todo: validate
    What is this for exactly   I'm not asking the user to input the amount of employees - i think it will work better if i can get the total number of rows and asign it automatically.

    Many Thanks again

  • grellsworth

    Thanks Derek for the help - it all works now! :)

    Thanks Jon also for the additional advice! I used the .Row solution. The first column of data is a unique identifer and should never be duplicated so i know when there is a blank row that it is the end of the data, so the z = Range("A1").End(xlDown) works great for me.

    Thanks again people for the help, much appreciated!

  • VSFW3

    hi Scott,

    That was my fault change

    z = Range("B4").End(xlDown).Count

    to just

    z = Range("B4").End(xlDown).

    Count gives the number of cells selected which is always 1.

    The line todo:validate is just to demonstrate that you can apply validation or anything to the value being stored in the global variable. Using global variables is never recommended but something you just have to use them. One of the reasons why they are not recommended is you cannot validate the value stored in a global variable. Using properties to access the global variable lets you validate.

    If your going to use the total number of rows then forget the global variable and just use a read only property, like this.

    Public Property Get EmployeeCount() As Double
    EmployeeCount = Range("B4").End(xlDown)
    End Property



  • Brian Kramer

    hey man,

    instead of using variables declared globally it's better to define the variable globally but access it through properties... if you create a new module and copy whats below you have a global variable that can be validated.

    Private glb_count As Integer

    Public Property Get GlobalCount() As Variant
    GlobalCount = glb_count
    End Property

    Public Property Let GlobalCount(ByVal vNewValue As Variant)
    'todo: validate
    glb_count = vNewValue
    End Property

    If the user needs to input the number of employees to process then prompt them once for the value and store it. Where you do this depends on your code, you might need to check if a value exists in each command button and only show the input box is there is no value, or you could do it when the form is first shown, depends.

    'get and validate user input

    Module1.GlobalCount = userInput

    You can use the property to get the variables value anywhere in your code, userform, macro, where ever.

    There is a short cut way to getting the number of rows in a sheet, but only if the data doesn't have empty rows in it.

    Range("B4").End(xlDown).Count

    This finds the number of rows in a row from cell b4 down.

    Hopefully that will be helpful.



  • Roman M.

    The line

      z = Range("B4").End(xlDown)

    relies on several assumptions:

    1. There are no empty cells in column B before the bottom of the block. If there are maybe the whole model is hosed, but if not, you might use

      Range("B65536").End(xlUp)

    which finds the lowest cell in column B with a value.

    2. The variable z is to be assigned the value of the default property of the Range object, which is .Value. Even if you are using the default property, it is better programming practice to explicitly state it. In any case, a better property to use here would probably be .Row (in case there are missing or duplicate values, or the column contains something other than a record index), and either save the final row number, or subtract the row number of the header row to get the number of actual records.

    - Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


  • Setting a global variable and referring to it from a private sub routine?
Answer this question