Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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...Sear ch&meta= NickHK "Bill Case" ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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" ... 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: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some general Tips and Tricks for programming !
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
20,000 Computer Tips n Tricks | New Users to Excel | |||
Sources of Excel Tips, Tricks & Code | Excel Discussion (Misc queries) | |||
Excel TIPS&TRICKS and Samples | Excel Discussion (Misc queries) | |||
Free Excel Tips & Tricks Webinar.... | Excel Discussion (Misc queries) | |||
Tips and Tricks | Excel Discussion (Misc queries) |