Public and Private refer to scope. Static refers to whether the varaible will
persist or not and hs nothing to do with scope. In the code you have posted
you could pass the sheet to the procedure something like this...

Public Sub StartMain()

On Error GoTo StartMain_Error '??? Goes nowhere

' Initiate variables; These are all declared static so that
' they persisit after a program crash

Static ThisExcelVersion As Variant 'Why a variable
Static Membsheet As Worksheet
Static ArchSheet As Worksheet

Set Membsheet = Worksheets("Member_List")
Set ArchSheet = Sheets("Archived_Members")

ThisExcelVersion = Application.Version
ActiveWorkbook.Colors(15) = RGB(241, 241, 221)
ActiveWorkbook.Colors(36) = RGB(255, 255, 211)

CheckSplitWindow Membsheet
LockMain

End Sub

Sub CheckSplitWindow(Membsheet As Worksheet)
' write test
' Dim MembSheet
Application.ScreenUpdating = False
Membsheet.Activate
With ActiveWindow
.SplitColumn = 4
.SplitRow = 4
End With
ActiveWindow.FreezePanes = True
End Sub

Or better yet you can use the code names of the sheets. In the VBE Project
Explorer you will see your sheets listed as

Sheet1(MySheet)

Sheet1 is the code name and MySheet is the tab name. You can change the code
name in the properties window to something more descriptive like shtMySheet.

You can refer to the sheets directly by their code names like this
msgbox Sheet1.Range("A1").value

This way you do not have to declare your sheets as variables...
--
HTH...

Jim Thomlinson


"Bill Case" wrote:

Hi NickHK;

My level of knowledge is what you might call 'gurubie' or 'newuru'. I've
used spreadsheets for years. Created quite a few macros, know a little 'C'
programming but this is an early venture into VBA. I know some things but
still get trapped with beginner stupidities. For example, I tried to take
Jim's advice and use Static variables rather than Public :

Public Static Sub StartMain()

On Error GoTo StartMain_Error

' Initiate variables; These are all declared static so that
' they persisit after a program crash

Static ThisExcelVersion As Variant
Static Membsheet As Worksheet
Static ArchSheet As Worksheet

Set Membsheet = Worksheets("Member_List")
Set ArchSheet = Sheets("Archived_Members")

ThisExcelVersion = Application.Version
ActiveWorkbook.Colors(15) = RGB(241, 241, 221)
ActiveWorkbook.Colors(36) = RGB(255, 255, 211)

CheckSplitWindow
LockMain

End Sub

Those Static variables shown above are variables that I want available
throughout my project to all modules and procedures. They were when I
declared them Public. They aren't now. Eg. the first procedure that is
called:

Sub CheckSplitWindow()
' write test
' Dim MembSheet

Application.ScreenUpdating = False
Membsheet.Activate
With ActiveWindow
.SplitColumn = 4
.SplitRow = 4
End With
ActiveWindow.FreezePanes = True

End Sub

Gives me a 'no variable defined' error. It seems to work when I 'Dim
MembSheet' in the CheckSplitWindow procedure. But, if I have to keep
re-declaring each global variable then they really aren't global, are they?
There is probably overkill on the Static and Public declarations, but I was
getting desparate.

Googled and read for over an hour before responding/posting with this new
problem.

Regards Bill


"NickHK" wrote:

Bill,
To me, something like Excel VBA is the easiset for someone to experience the
power and ease of (starting to) proramming. You have the macro recorder to
guide you and Intelisense certainly helps.
I hope you have learnt the benefit of frequent saves ? <g

NickHK

"Bill Case" ...
Thnak you Jim, NickHK, semiopen;

I followed Jim's suggestion and revisited the scope of most of my
procedures
and startup variables. Semiopen's suggestion is the type of thing I was
looking for. I'll do something like that on my next project.

NickHk told me about some things I had already set up but with the wrong
scope (Public vs Static). I was losing my object definitions for WS etc.
NickHK asked what I was doing to cause so many crashes. Well, I am not a
programmer; I just completed a small database/spreadsheet project for a
club
I am a member of. It works. They are happy. But as I learned more, I
got
curious about how things work so I started to change the code around to
see
what I could learn and if I could make it faster and neater. That can
cause
a lot of crashes.

"If it ain't broke, don't fix it."

Regards Bill

"semiopen" wrote:


Bill Case wrote:
Hi;

While I am programming (experimenting) with VBA I have lots of crashes
as I
try different things out. When a program crashes it loses all its
public
setup variables and other entered data. Is there some general
programing
tips to keep in mind so that I can setup my playing around so that I
can
recover from a crash without having to close down the program and start
up
again?

Just some general good practices tips.

I am using the MZ-tools add-on with VBE; but does anybody know of a
good
Auto-complete tool I can add-on that helps complete non-VBA key words.
VBE
has lots of that assistance, but I want something that helps complete
my own
varibale, function and procedures names?

Regards Bill

If you want data to persist between crashes - put it in the
spreadsheet. For each global that you use, say "foo", You can manually
create a range named "foo" on your spreadsheet. Whenever you have an
assignment "foo = val", you can echo it immediately with
Range("foo").Value = foo

Then - you can write a sub called say Restore() like:

Sub Restore()
foo = Range("foo").Value
bar = Range("bar").Value
'etc.
End Sub

Then - just run Restore after a crash. I think you can even run it from
the immediate window while in break mode - but with possibly strange
semantics.

I don't know any (easy) way to automate the process - but if you are
not talking about too many variables (as the phrase "playing around"
suggests) it shouldn't involve all that much typing overhead.

Just an idea

-semiopen