I'm using Conditional for Early/Late binding of Scripting.Dictionary. But never thought about using it with Debug.Assert. That is really nice. I'm going to slowly integrate that into my code. On another note, is there a way to have like a predefined dictionary? In Python I just do dc = {"one": 1, "two": 2} globally and then in def main() function I just call print(dc.get("one")). My problem is in Ribbon I've got 10 buttons which calls 10 different forms and each of them needs this global dict. So for now I'm doing it like "if dict doesn't exists, initialize with these values" in each form after init.
@@micheltw To be honest, I would actually strongly advise against this technique for two reasons: 1) All the "DebugAssert" Function calls will clutter your code even more than the already verbose nature of VBA requires anyway 2) You build behaviour into your program where functions behave differently when being debugged as opposed to being called by the user. This can make certain errors a lot harder to find as they will not even appear during the debugging run, but will appear when called by the user - basically your own program will pull a Volkswagen on you. My approach is to have all the functions raise appropriate errors that I need anyway to correctly pass errors along the flow of control and then write Unit-Tests in a separate module that tests the function individually and checks the error condition. It is still cluttered as you need a lot of boilerplate-code in the test-module, but that way it is all in the test module and the production code stays clean. If you are willing to put in the effort (and have a sufficient amount of masochistic tendencies) you can even write your own testing-framework that takes care of the boilerplate-code and you can even have clean test-modules.
Thanks, Paul. In an ocean of VBA tutorials, "how-to"s & guides, yours are the life-saving islands i always end up on. :) Yours are the guides I bookmark and keep returning to. And, like this one, they are the guides that teach me something new. I've passed Debug.Assert many a time, not understanding what it did. Another clear and thorough guide from you and I have a new powerful weapn in my toolbox. Brilliant! Cheers and thanks from Down Under. 🙏☮
Dude - I've been writing VBA (mostly Access VBA) for years. Your videos have been an eye opener for me. I've basically went back and changed all of my "macros" and added your suggestions. My Excel macros now run much faster thanks to your suggestions. I'll make sure to watch your videos as much as I can. Again, THANKS!
Great content as always, Paul. And thanks for the tip about #If. When creating a new Do… Loop, I always, always, that is, always, put in the line Debug.Assert False before the Loop statement to save me from accidentally getting stuck in endless loops. Other than that I have used Debug.Assert occasionally, but will make more systematic use of it now that I’ve seen what it can do.
Not sure if after so many years of using "if ... then ... stop", I will switch to "debug.assert", but I will definately be using the # and Conditional Compilation Arguments, which is a completely new learning for me. Thank you!
You don't even know how much I waited for the next video from You! And I can see some places in my codes for Assert, never used that before, thanks for knowledge!
I have not used it because I did not know what it was. I learned not only how to use it, but also how to set a project variable, which I've never done before, either. Thank you for providing this video!
Hi Paul. Catching up on some vids I missed last quarter. Awesome lesson on Debug.Assert! Thanks for the great tips and tricks on how and when to use it and how to turn it on/off.. great stuff :)) Thumbs up and Happy New Year!!
Thanks Paul. I have seen debug.assert when using debug.print and always wondered why it was used for. Thanks for explaining. I might try to place this in my next code.
@@Excelmacromastery You've really changed how much I use arrays when dealing with data, particularly from an external file. At present i'm dumping to a worksheet to use later. Is it possible to keep the array in memory for access whilst the working file is open rather than dump the data, and if so is it advisable way of working. Each time i've tried it I lose reference to the variable
Wouldn't it be easier to just add "Exit Sub" as the first line item on the DebugAssert subroutine when you don't want to use debug assert commands? Adding a conditional compile statement to trigger the code seems like something you forget to do or if someone else looks at your code and tries to understand it. You can also put in a comment on the "Exit Sub" line to make it easier for someone else to understand why you have that code there. I always believe in less code and not having to look for all the hidden conditional compile that you have to type every time to turn on and off. It just seems a lot simpler.
Nice video. Good job. One quibble near the end... you state that Debug.Assert does not slow the execution down when debugging=0. That's not entirely true, especially when you encapsulate the the assertion into a dedicated procedure. Calling any procedure has a significant cost and should the calling of a procedure happen within a tight loop, the cost of calling it can really add up. I code the same way and I would recommend doing exactly what you have shown. My only caveat is that the coder needs to understand that calling the Assert method of Debug, even with debugging=0, is fairly cheap (BUT NOT FREE), but it could be a performance issue if it is done in a tight loop. This issue is even more potentially problematic if the assert is encapsulated in a dedicated procedure (as you demonstrated in the video as a way to turn the assertions off). But again, it is likely only a concern if called from within a tight loop. So it does slow the execution down, but the gains are most likely worth it. That being said, coders need to ALWAYS be careful about what is allowed to be included within a tight loop.
Thanks for the feedback Daniel. You are right - it is important to be aware of tight loops when using asserts. I tend not to use asserts in these type of loops myself unless I am debugging a loop. If someone wanted to use the assert in a tight loop they could us the "#If" within the loop itself and this would solve the performance cost problem although it makes the code a bit more verbose.
Hi, thanks for your videos and great site. I apprecate if you 'd put some of your excelent complete guides on VBA text file handling!? 🙏 (both direct VBA and file system object)😀
thank you Paul :) I was asking myself what this method does. It is a simple version of "If condition Then Stop" - or I see it like this. For me "If condition Then Stop" is more expressive than debug.assert . I suppose that I will not use this method.
Thanks for the feedback Ionut. One advantage of Debug.Assert is that you can update custom DebugAssert function so that it takes text. This makes it similar to Debug.Assert in other languages like C# e.g. Debug.Assert(type != null, "Type parameter is null");
"Set" is used for objects such as Excel objects, class modules or external libraries. See the "How VBA Objects Really Work" video here th-cam.com/video/-sj0vvYT-Mk/w-d-xo.html.
Thank you so much for your guidelines.... I have a small query , could you please make a video where we fix the date format where user have many date format ....can we fix it.
Is that the same "author" property you can get in windows explorer when right-clicking the file and selecting properties? If so, I would say it's a bit risky to enable debugging based on that alone. My username for example is just my first name on my home PC, which isn't exactly unique.
You can set it but this code will still perform the check when you give it to the user and may impact the performance.. If you use #If the code will not be included and so it won't impact the speed of the application.
Thanks for all ur videos. I am a professional VBA developer. Recently I made a macro which was running fine but suddenly when users are opening it says can't find project Or library. Whe I try to open tools den references it is not opening and macro buttons are also not working. What can be the solution if u can help. Thanks in advance
Hi Paul, Hope you are doing well , i have been following your videos and learned a lot form ur video . now i need ur help. my project is SAP Analysis AO template refresh using vba . can you please make vide on how to refresh AO reporting template using vba code .
hi.teacher you explained it Well,thang you very much.teacher What to do When there is an error in the UserForm . error this run time '9': subscript out of range . please We Would say Why this error is caused . Thank you in advance
FAO "Excel Macro Mastery" I am crying out for help with an issue I have had with some of my VBA spreadsheets. The issue I have is very unusual and I have researched the problem many times without success. The spreadsheets(s) in question have been problem free for years, then suddenly they started to give me intermittent problems with Userforms freezing for unknown reason. Basically the userform will partly load and freeze, and will only unlock if i take the power cable out the PC then replace it. The issue with userforms not loading seems to affect other spreadsheets with their userform too. It happens on all PC's and even using different versions of Excel. I have had the issue with Excel 2016 and on my work laptop with Excel 2019. Even when I manage to unlock the userform by taking out the power cable and replacing it, I still need to close the spreadsheet down and reopen it, to be able to continue working with the spreadsheet. If I dont do this, the userform will lock every time i try to load it. I have even had a brand new laptop and clean installations of Excel and it still continues to randomly freeze on the userforms. Sometimes the userform will load correctly and while using the form it can lock up. The spreadsheet was originally developed in 2015 and as I say ran for years without this issue. This spreadsheet is a template spreadsheet which I use as a costing tool and is saved as a product. I can therefore open much older versions dating back to 2015 and even these spreadsheets freeze. This therefore eliminates any updates to the code I may have made over the years. I have now had to live with this issue since the spreadsheet would take many months to rebuild from scratch since it is very complex in nature.
@@saulaxel Thanks for the reply. There is so much more I could write because I have tried many times to overcome this problem. I have considered introducing code to delay the form being displayed while the updates to the form are happening so maybe that is what you are meaning. That did not help. Its intermittent but once the problem occurs, I am stuck with it happening constantly for that day. Even when I reload my PC the problem seems to stay, it is very odd. I have even re-written the code that fills in the userform before it is loaded, and that seemed to help for a while but the problem came back. The method of unplugging then re plugging in the power cable seems to me to be a clue. I mean why would doing that cause the form to refresh?! It is like the code has got stuck. I can sometimes "break" the execution of the code when it has frozen (does not always work), and the code is stopped at the line UserForm1.Show, and the VBA editor is also frozen at the point, perhaps another clue. Again the only way out from there is the power cable method.
Hi Adam, That sounds like a very strange issue. I'm not sure what would cause the UserForms to freeze like that especially as they worked before for you. Is it possible there is a minimized modal UserForm somewhere that is preventing access to the rest of Excel? Also is it possible to break into the code using the Ctrl + Break equivalent on your computer?
@@Excelmacromastery I am pretty sure that I have had this issue when it is the first file used after loading windows and the first userform used. I am not 100% sure. I have even placed Unload Userform1, 2, 3, 4 etc as the first code to run when I click the button that loads userform1. The issue still can happen. Yes "sometimes" Ctrl+Break does go into the code and it highlights the line Userform1.show, which of course is the form that is freezing, but is again frozen on that line. Another clue something behind the scenes is in a loop perhaps? Today I had a new theory. My macro loads Userform1. I wondered if the fact I use other spreadsheets that have the same named forms. I am going to rename the default names and see if that helps. What is also odd is I can be in the userform and in a text box in the form and it freezes unexpectedly. Again to release the frozen form, taking the power cable out and putting it back in can eventually unfreeze it. One extra clue is that after a lot of jiggery pokery with the code, I can sometimes release the frozen form by clicking "manage notifications" bottom right of task bar. Also remember this issue seems to affect other spreadsheets too, but much less often. I wonder if there is some hidden code that is common to my VBA spreadsheets that is corrupted. I dont know if that is a silly suggestion, I dont know much how Excel works behind the scenes. I seem to have lessened the freezes over the last few months by small modifications to code as I think of new ideas, but the problem still persists and is a massive hinderance and frustrating.
When the spreadsheet has been released using the power cord, i have to shut the spreadsheet down and reopen it. If I try to reload the form without first closing it and reopening it, it will always instantly freeze 100% of the time. I tried creating a fix button with code unloading all userforms that I use in this spreadsheet to see if clicking that button will allow me to reset the forms and not have to close the spreadsheet and reopen it. It has helped a bit, it sometimes works lol. VERY VERY ODD!!
How about instead of calling DebugAssert directly, call Debug.Assert(DebugAssert(condition)), and then let the DebugAssert-function always return True if not in debug mode. That way the break will occur in the desired location. For example : Debug.Assert (DebugAssert(amount > 0) )
The problem is that Debug.Assert will be in the code even in release mode and also the code is slightly more complex to read. That said, these are minor issues and if you prefer to do it that way I don't think it will cause any major problems.
I wouldnt go and set debugging=1 but I would check the current user name and if it is my name I‘d turn assertions on instead. No need to remember turning it on or off :)
Have you heard about Rubberduck Project rubberduckvba.com/? One of its many features are unit tests known from all other programming languages. But there are more, you should definitely give it a try! Best regards!
Hello i would like to thank u for all contents u share : i wish i can write some good code like u : i have a question about using adodb connection on company network : strangely, when i open connection on my workbook , it's only works when i first open a shared workbook on network of my company ( others connections on my desktop crash Excel... ) Have u a idea of this problem ?
Hi Everyone, I hope you enjoy this video. Let me know in the comments if you plan to use Debug.Assert in the future.
I'm using Conditional for Early/Late binding of Scripting.Dictionary. But never thought about using it with Debug.Assert. That is really nice. I'm going to slowly integrate that into my code.
On another note, is there a way to have like a predefined dictionary? In Python I just do dc = {"one": 1, "two": 2} globally and then in def main() function I just call print(dc.get("one")). My problem is in Ribbon I've got 10 buttons which calls 10 different forms and each of them needs this global dict. So for now I'm doing it like "if dict doesn't exists, initialize with these values" in each form after init.
@@songokussj4cz There isn't a way to predefine the values for the scripting dictionary .
As I am building more and more complex macro, I will be using this newly acquired piece of knowledge, thank you
@@micheltw To be honest, I would actually strongly advise against this technique for two reasons:
1) All the "DebugAssert" Function calls will clutter your code even more than the already verbose nature of VBA requires anyway
2) You build behaviour into your program where functions behave differently when being debugged as opposed to being called by the user. This can make certain errors a lot harder to find as they will not even appear during the debugging run, but will appear when called by the user - basically your own program will pull a Volkswagen on you.
My approach is to have all the functions raise appropriate errors that I need anyway to correctly pass errors along the flow of control and then write Unit-Tests in a separate module that tests the function individually and checks the error condition.
It is still cluttered as you need a lot of boilerplate-code in the test-module, but that way it is all in the test module and the production code stays clean.
If you are willing to put in the effort (and have a sufficient amount of masochistic tendencies) you can even write your own testing-framework that takes care of the boilerplate-code and you can even have clean test-modules.
Thanks, Paul. In an ocean of VBA tutorials, "how-to"s & guides, yours are the life-saving islands i always end up on. :)
Yours are the guides I bookmark and keep returning to.
And, like this one, they are the guides that teach me something new.
I've passed Debug.Assert many a time, not understanding what it did. Another clear and thorough guide from you and I have a new powerful weapn in my toolbox. Brilliant!
Cheers and thanks from Down Under. 🙏☮
i had been using if () then Stop. This is a much more elegant shortcut. Thanks.
You're welcome.
Dude - I've been writing VBA (mostly Access VBA) for years. Your videos have been an eye opener for me. I've basically went back and changed all of my "macros" and added your suggestions. My Excel macros now run much faster thanks to your suggestions. I'll make sure to watch your videos as much as I can. Again, THANKS!
You're welcome John
Great content as always, Paul. And thanks for the tip about #If. When creating a new Do… Loop, I always, always, that is, always, put in the line Debug.Assert False before the Loop statement to save me from accidentally getting stuck in endless loops. Other than that I have used Debug.Assert occasionally, but will make more systematic use of it now that I’ve seen what it can do.
Not sure if after so many years of using "if ... then ... stop", I will switch to "debug.assert", but I will definately be using the # and Conditional Compilation Arguments, which is a completely new learning for me. Thank you!
You're welcome Roland.
You don't even know how much I waited for the next video from You! And I can see some places in my codes for Assert, never used that before, thanks for knowledge!
Great to hear!
I have not used it because I did not know what it was. I learned not only how to use it, but also how to set a project variable, which I've never done before, either. Thank you for providing this video!
Glad it was helpful!
I really like your type of teaching and your british accent! Keep on going !
He's Irish
Wow - writing code for years (just for myself) - never knew about this - seems awesome!
Glad it was helpful!
Thank you Paul. Will definitely try using Debug.Assert going forward.
The Debug Assert and # information was very helpful. Thank you so much
Looks exactly what I need to use straight away - Thank you sir.
Hi Paul. Catching up on some vids I missed last quarter. Awesome lesson on Debug.Assert! Thanks for the great tips and tricks on how and when to use it and how to turn it on/off.. great stuff :)) Thumbs up and Happy New Year!!
Thanks Wayne. You're very welcome and a very Happy New Year to you as well.
Thanks Paul. I have seen debug.assert when using debug.print and always wondered why it was used for. Thanks for explaining. I might try to place this in my next code.
You're welcome Kevin.
I have never used Debug Assert, but I will now. Thanks!
You're welcome Gregory Taylor
Sir, excellently explained with examples it will help very much for learners.
Keep it up
Looking forward 👍
Mannn.... You are the legend of vba
Thanks. Glad you like it.
I did not know about this function, thank you so much for teaching me!
Thank you very much for this video, it is really usefull and well explained !
U r nothing less than bliss Mr. Paul.
Thanks Ashish
Thank you, Paul - useful as always.
Very welcome
Hi Paul. Thank you very much for another very useful video. I will certainly use it.
Great to hear João
As usual, great amazing content. Thank you Paul.
You're welcome Fy
This is wonderful! Thanks Paul!
Glad you like it.
Would like a video which shows you basic setup of your projects. Where do you store your constants, what are your standard modules etc
Hi Chris, I cover this in The Excel VBA Handbook course.
@@Excelmacromastery You've really changed how much I use arrays when dealing with data, particularly from an external file. At present i'm dumping to a worksheet to use later. Is it possible to keep the array in memory for access whilst the working file is open rather than dump the data, and if so is it advisable way of working. Each time i've tried it I lose reference to the variable
Great, useful and practical! Thank you!!
Glad it was helpful!
Is it only me that discovered the CTRL+L shortcut just now? Or you don't see how powerful it is yet? My jaw is still on the floor.
Great stuff. Many thanks!
Nice one, Paul. Thanks!
You're welcome John.
Great video, thanks Paul.
Thanks.
Muchas gracias Paul, una instrucción muy útil.
You're welcome.
Thanks, i didnt know this functionality
Sir U are the inspiration
Excellent! thanks a lot!
Thanks Paul.
I have a runtime error Autofilter Range Method failed? What are the Cause? Or how to fix this?
Thank you ... I WILL use this in the future.
Glad you like it Patrick.
Wouldn't it be easier to just add "Exit Sub" as the first line item on the DebugAssert subroutine when you don't want to use debug assert commands? Adding a conditional compile statement to trigger the code seems like something you forget to do or if someone else looks at your code and tries to understand it. You can also put in a comment on the "Exit Sub" line to make it easier for someone else to understand why you have that code there. I always believe in less code and not having to look for all the hidden conditional compile that you have to type every time to turn on and off. It just seems a lot simpler.
Thanks a lot 🙂
Nice video. Good job. One quibble near the end... you state that Debug.Assert does not slow the execution down when debugging=0. That's not entirely true, especially when you encapsulate the the assertion into a dedicated procedure. Calling any procedure has a significant cost and should the calling of a procedure happen within a tight loop, the cost of calling it can really add up. I code the same way and I would recommend doing exactly what you have shown. My only caveat is that the coder needs to understand that calling the Assert method of Debug, even with debugging=0, is fairly cheap (BUT NOT FREE), but it could be a performance issue if it is done in a tight loop. This issue is even more potentially problematic if the assert is encapsulated in a dedicated procedure (as you demonstrated in the video as a way to turn the assertions off). But again, it is likely only a concern if called from within a tight loop. So it does slow the execution down, but the gains are most likely worth it. That being said, coders need to ALWAYS be careful about what is allowed to be included within a tight loop.
Thanks for the feedback Daniel. You are right - it is important to be aware of tight loops when using asserts.
I tend not to use asserts in these type of loops myself unless I am debugging a loop.
If someone wanted to use the assert in a tight loop they could us the "#If" within the loop itself and this would solve the performance cost problem although it makes the code a bit more verbose.
Nice trick. Thanks for sharing that.
Happy to help
thanks, very useful video
Until now I always used F8 to step trought the code and manually check the variables 😄
Thank you so much for this video
Never used it, I am new to coding in VBA but it will be in my tool box from now on.
Hi, thanks for your videos and great site. I apprecate if you 'd put some of your excelent complete guides on VBA text file handling!? 🙏 (both direct VBA and file system object)😀
Many thanks Paul
You're welcome.
thank you Paul :) I was asking myself what this method does. It is a simple version of "If condition Then Stop" - or I see it like this. For me "If condition Then Stop" is more expressive than debug.assert . I suppose that I will not use this method.
Thanks for the feedback Ionut. One advantage of Debug.Assert is that you can update custom DebugAssert function so that it takes text. This makes it similar to Debug.Assert in other languages like C#
e.g.
Debug.Assert(type != null, "Type parameter is null");
If you think of it as "keep going if..." you may find more use for it
Great. I will definitely give it atrial
Interesting at least. Do you have a video about the Variant declaration and when to assign with or without 'Set' ? That is still a mystery to me.
I'm not a expert, but you use set when you are using objects. An object can be a userform, a control a class module
"Set" is used for objects such as Excel objects, class modules or external libraries. See the "How VBA Objects Really Work" video here th-cam.com/video/-sj0vvYT-Mk/w-d-xo.html.
Thanks, so useful content
Glad it was helpful!
Thank you so much for your guidelines.... I have a small query , could you please make a video where we fix the date format where user have many date format ....can we fix it.
Wise or unwise:
In workbook_open() could we set debugging=1 if Application.UserName = ThisWorkbook.BuiltinDocumentProperties(“Author”) ?
Is that the same "author" property you can get in windows explorer when right-clicking the file and selecting properties? If so, I would say it's a bit risky to enable debugging based on that alone. My username for example is just my first name on my home PC, which isn't exactly unique.
You can set it but this code will still perform the check when you give it to the user and may impact the performance.. If you use #If the code will not be included and so it won't impact the speed of the application.
Thanks for all ur videos. I am a professional VBA developer. Recently I made a macro which was running fine but suddenly when users are opening it says can't find project Or library. Whe I try to open tools den references it is not opening and macro buttons are also not working. What can be the solution if u can help. Thanks in advance
Hi Paul, Hope you are doing well , i have been following your videos and learned a lot form ur video . now i need ur help. my project is SAP Analysis AO template refresh using vba . can you please make vide on how to refresh AO reporting template using vba code .
Thank you sir.
Most welcome
hi.teacher you explained it Well,thang you very much.teacher
What to do When there is an error in the UserForm . error this run time '9':
subscript out of range .
please We Would say Why this error is caused . Thank you in advance
11:16 .print not .assert in code
Yes, a small typo there.
FAO "Excel Macro Mastery" I am crying out for help with an issue I have had with some of my VBA spreadsheets. The issue I have is very unusual and I have researched the problem many times without success. The spreadsheets(s) in question have been problem free for years, then suddenly they started to give me intermittent problems with Userforms freezing for unknown reason. Basically the userform will partly load and freeze, and will only unlock if i take the power cable out the PC then replace it. The issue with userforms not loading seems to affect other spreadsheets with their userform too. It happens on all PC's and even using different versions of Excel. I have had the issue with Excel 2016 and on my work laptop with Excel 2019. Even when I manage to unlock the userform by taking out the power cable and replacing it, I still need to close the spreadsheet down and reopen it, to be able to continue working with the spreadsheet. If I dont do this, the userform will lock every time i try to load it. I have even had a brand new laptop and clean installations of Excel and it still continues to randomly freeze on the userforms. Sometimes the userform will load correctly and while using the form it can lock up. The spreadsheet was originally developed in 2015 and as I say ran for years without this issue. This spreadsheet is a template spreadsheet which I use as a costing tool and is saved as a product. I can therefore open much older versions dating back to 2015 and even these spreadsheets freeze. This therefore eliminates any updates to the code I may have made over the years. I have now had to live with this issue since the spreadsheet would take many months to rebuild from scratch since it is very complex in nature.
The only possible cause that comes to my mind is time handling code, but I don't know
@@saulaxel Thanks for the reply. There is so much more I could write because I have tried many times to overcome this problem. I have considered introducing code to delay the form being displayed while the updates to the form are happening so maybe that is what you are meaning. That did not help. Its intermittent but once the problem occurs, I am stuck with it happening constantly for that day. Even when I reload my PC the problem seems to stay, it is very odd. I have even re-written the code that fills in the userform before it is loaded, and that seemed to help for a while but the problem came back. The method of unplugging then re plugging in the power cable seems to me to be a clue. I mean why would doing that cause the form to refresh?! It is like the code has got stuck. I can sometimes "break" the execution of the code when it has frozen (does not always work), and the code is stopped at the line UserForm1.Show, and the VBA editor is also frozen at the point, perhaps another clue. Again the only way out from there is the power cable method.
Hi Adam,
That sounds like a very strange issue. I'm not sure what would cause the UserForms to freeze like that especially as they worked before for you. Is it possible there is a minimized modal UserForm somewhere that is preventing access to the rest of Excel?
Also is it possible to break into the code using the Ctrl + Break equivalent on your computer?
@@Excelmacromastery I am pretty sure that I have had this issue when it is the first file used after loading windows and the first userform used. I am not 100% sure. I have even placed Unload Userform1, 2, 3, 4 etc as the first code to run when I click the button that loads userform1. The issue still can happen. Yes "sometimes" Ctrl+Break does go into the code and it highlights the line Userform1.show, which of course is the form that is freezing, but is again frozen on that line. Another clue something behind the scenes is in a loop perhaps? Today I had a new theory. My macro loads Userform1. I wondered if the fact I use other spreadsheets that have the same named forms. I am going to rename the default names and see if that helps. What is also odd is I can be in the userform and in a text box in the form and it freezes unexpectedly. Again to release the frozen form, taking the power cable out and putting it back in can eventually unfreeze it. One extra clue is that after a lot of jiggery pokery with the code, I can sometimes release the frozen form by clicking "manage notifications" bottom right of task bar. Also remember this issue seems to affect other spreadsheets too, but much less often. I wonder if there is some hidden code that is common to my VBA spreadsheets that is corrupted. I dont know if that is a silly suggestion, I dont know much how Excel works behind the scenes. I seem to have lessened the freezes over the last few months by small modifications to code as I think of new ideas, but the problem still persists and is a massive hinderance and frustrating.
When the spreadsheet has been released using the power cord, i have to shut the spreadsheet down and reopen it. If I try to reload the form without first closing it and reopening it, it will always instantly freeze 100% of the time.
I tried creating a fix button with code unloading all userforms that I use in this spreadsheet to see if clicking that button will allow me to reset the forms and not have to close the spreadsheet and reopen it. It has helped a bit, it sometimes works lol. VERY VERY ODD!!
is there no sound?
There is sound
on error resume next
think this prevents all errors.
How about instead of calling DebugAssert directly, call Debug.Assert(DebugAssert(condition)), and then let the DebugAssert-function always return True if not in debug mode. That way the break will occur in the desired location. For example : Debug.Assert (DebugAssert(amount > 0) )
The problem is that Debug.Assert will be in the code even in release mode and also the code is slightly more complex to read. That said, these are minor issues and if you prefer to do it that way I don't think it will cause any major problems.
I wouldnt go and set debugging=1 but I would check the current user name and if it is my name I‘d turn assertions on instead. No need to remember turning it on or off :)
The problem is that the code is used in the release version. If you use #IF then the code isn't used when it's turned off.
I like that thankyou
You're welcome Steve.
Hi, Paul.
Hi Joao.
for the last 10%... on error resume next
I'm not sure what you mean?
@@Excelmacromastery It was a joke. On error resume next will of course reduce VBA errors to 0.
Find vlooup match vba suggestions required
Not clear on your comment.
I think I would rather just find Debug.Assert and replace it with 'Debug.Assert.
Have you heard about Rubberduck Project rubberduckvba.com/? One of its many features are unit tests known from all other programming languages.
But there are more, you should definitely give it a try! Best regards!
Mz Tools🤔
Hello i would like to thank u for all contents u share : i wish i can write some good code like u : i have a question about using adodb connection on company network : strangely, when i open connection on my workbook , it's only works when i first open a shared workbook on network of my company ( others connections on my desktop crash Excel... ) Have u a idea of this problem ?