Re: Call Subs from other Sheets
You don't have to activate your sheets to run the code.
You can create an object which is your work sheet and then pass that object to your subroutines.
Make sure that your routines are in a code module so that they are available to all the sheets in your work book. If you place code in each sheet, those subroutines are available to that sheet only, in a code module they are available to all the sheets.
For example, a function definition may look like so:
Function HeadSheet(This_Worksheet As Worksheet, ConsultantName As String, StartYear As Integer) As Integer
I want to head up a series of sheets in an identical manner. I call this function in my main code like this:
Function Working_Hours_Summary(Summary_Sheet As Worksheet, Consultant As String, StartYear As Integer, Working_Hours() As Work_History, Index_File As Integer) As Integer
...
...
CurrentRow = HeadSheet(Summary_Sheet, Consultant, StartYear) + 1
'*
'* Working Hours Titles
'*
Application.StatusBar = "Summary - Working Hours"
Call Fill_Cell(Summary_Sheet, CellAddress(c_Summary_Title_Hours, CurrentRow), c_text_format, c_EMS_Title, True)
Or like this:
Set Expense_Sheet = Worksheets(Sheet_Name)
Start_Row = HeadSheet(Expense_Sheet, Consultant, Start)
So by passing the sheet that I wish to use, I don't have to activate the sheet.
Any macros that you have attached to the sheet events will be triggered and you can access the various properties of the sheets as well:
If This_Worksheet.Range(c_Exp_Date & Row).Value = "" Then
'*
'* Add reason and values etc to previous row
'*
Expenses(Expenses(0).Max).Reason = Expenses(Expenses(0).Max).Reason & " " & This_Worksheet.Range(c_Exp_Reason & Row).Value
Else
So This_Worksheet can be any worksheet that I throw at the subroutine.
As you're new to VBA, a couple of tips.
In all your modules, you should always have this as the first line:
This statement means that you must ALWAYS declare your variables likie this:
Dim Expense_Count As Integer
Dim This_Expense As Integer
Dim Sub_Count As Integer
ReDim Subset(1) As Expense_List
Dim New_Exp As Boolean
This can prevent a lot of problems where you try to use a variable for instance and mistype the name - Hell0 instead of Hello for example.
Also, always type your variables and functions etc using the AS keyword. Don't be tempted to be lazy and just use a Variant - ie always say if it is a string, an integer whatever. This will give you a speed increase at run time because Excel then knows straight away what type of data it is dealing with and doesn't have to coerce one type to another.
Do not use Global variables. Global variables are trouble just waiting to happen. Always use local variables if you can. If you can't document why you've used them and use them carefully.
Lastly, use comments. Readable comments that give information about what is going on. Don't comment the obvious, don't use comments that are just copies of the code in normal language. Describe what the code does, what side effects it may have, it's limits and the circumstances in which it will not work.
Have a look here for examples of what I'm talking about above.
I hope you enjoy learning the art of programming!
Regards
Rich
