Hi Jim et al;

Jim that was what I wanted. Just renaming the 'code' name for my sheets
makes things much simpler -- didn't know you could do that. I thought I
might have to reindex them as well or something.

When something crashes, I still have my sheets etc. showing so all I needed
to do was build a little macro that re-runs my startup procedures -- without
getting stopped by global variables because I could now get rid of them all.
And, I was back in business -- destroying some more routines that already
worked.

Regards Bill

"Jim Thomlinson" wrote:

Sorry I forgot to mention that nothing persists after a crash. The heap
(where persisting varaibles are stored) is cleared in the event of a crash or
if the stand alone code line "End" is executed.
--
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