Board index » Excel » Need some general Tips and Tricks for programming !

Excel29
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


-

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

--

HTH...



Jim Thomlinson





"Bill Case" wrote:

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



"Bill Case" <BillCase@discussions.microsoft.com>¼¶¼g©ó¶l¥ó·s»D:BDA8D8DA-2860-40D4-B1F8-90E719D83614@microsoft.com...



-

Bill Case wrote:

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



-

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,

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" <BillCase@discussions.microsoft.com>¼¶¼g©ó¶l¥ó·s»D:F79EB1A3-0298-438A-B0EB-FC2BD1C436ED@microsoft.com...



-

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.







Bill Case wrote:

--



Dave Peterson

-

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



"Dave Peterson" <petersod@verizonXSPAM.net>

???????:451D7E46.25B815E3@verizonXSPAM.net...



-

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:

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:

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