Need some general Tips and Tricks for programming !

[Guest]

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

 

[Guest]

The biggest tip I can give you here is to avoid using global variables as
much as possible. For the vast majority of things you will want to do you can
pass local variables (and declare them as static if you need the value to
persist after the procedure ends). You have just experienced one of the
downfalls of using globals (they are cleared when the VBA crashes). They are
also cleared if you have the stand alone line "End" anywhere in your code...
The other problem with globals is that they are a beast to debug. If 10
different procedures all use one global and at some point during the
exectution the value is not what it is supposed to be then you have to try to
figure out which procedure modified it last (this is often darn near
impossible).

I tend to use globals primarily to hold information that will be added once
during execution and then just read there after. Such as capturing a password
from the user to run queries against protected database tables. If at any
point the value is cleared I can just reprompt the user for the value... A
little anoying for the user but certainly not fatal...

 

[NickHK]

Bill.
I don't know what you are doing to crash Excel on a regular basis, but...

As well as well as Jim's sage advice, try be clear which workbook,
worksheet, range etc you are working with.
Unless you need the flexability of Activecell, ActiveWorkBook, Selection and
unqualified Ranges, explicitly state which WB/WS you are working with. You
can create your own local WS variable(s) to make it more clear and set those
to the objects (WS in this case) that you are working with.
Dim WS_Source As WorkSheet
Dim WS_Destination As WorkSheet
especially when you come back 3 years later to revisit your own code, or
send it here, where people have little idea of your concept.

For discreet processing, put the logic in a function and return the
resulting value. Then when you (invariably) find a better to do the
processing, you change the code in only one place.

There's certainly more, but I doubt you want too much immediately.
This gives various people's concepts
http://www.google.co.uk/search?hl=en&q=VBA+programming+standards&btnG=Search&meta=

NickHK

 

[semiopen]

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

 

[Guest]

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

 

[NickHK]

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

 

[Dave Peterson]

If you must use persistent variables, maybe you can create a routine that
initializes them.

Public VariablesAreInitialized as boolean

====
Then you can use

if variablesareinitialized then
'keep going
else
call thatroutinethatinitializesthevariables
'include
'VariablesAreInitialized = true
'in that routine
end if

And make sure you force yourself to declare your variables.

Saved from an earlier post about why "option explicit" should be used.

I do it for a much more selfish reason.

If I add "Option Explicit" to the top of a module (or have the VBE do it for me
via tools|options|Editor tab|check require variable declaration), I know that
most of my typos will stop my code from compiling.

Then I don't have to spend minutes/hours looking at code like this:
ctr1 = ctrl + 1
(One is ctr-one and one is ctr-ell)
trying to find why my counter isn't incrementing.

And if I declare my variables nicely:

Dim wks as worksheet
not
dim wks as object
and not
dim wks as variant

I get to use the VBE's intellisense.

If I use "dim wks as worksheet", then I can type:
wks.
(including the dot)
and the VBE will pop up a list of all the properties and methods that I can
use. It saves time coding (for me anyway).

And one final selfish reason.

If I use a variable like:

Dim ThisIsACounterOfValidResponses as Long

I can type
Thisis
and hit ctrl-space and the VBE will either complete the variable name or give me
a list of things that start with those characters.

And by using a combination of upper and lower case letters in my variables, the
VBE will match the case found in the declaration statement.

ps. From what I've read, if you declare a variable as Integer, the modern pc
will have to spend time converting it to long. So I've stopped using "dim x as
integer". It's safer for me and quicker for the pc.

 

[NickHK]

Dave,
If everyone followed your advise concerniung "Option Explicit", I'm sure it
would cut by 50% the "It dooes noes work.." questions that come to this NG.

NickHK

 

[Guest]

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

 

[Guest]

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...

 

[Guest]

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.

 

[Guest]

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