Improve Speed of Codes in Workbook

Please do not take this as criticism, in post #3 of the link below, I was trying to subtly point out the case you would face with the track you were taking.

https://www.excelforum.com/excel-pro...ml#post5711789

Excel is a very poor graphics program - you are coupling an artistic interface with multiple sheets some of which have formula in that the entirety calculate at every data entry.
All of this is unnecessary 'bloat' - the nature of your app is that of a 'pseudo' database - the primary objective is the storage of data - use your sheets in the most simple/basic form you can - get rid of on sheet formula that react to every input.
The 'artistic' refreshing of sheet formatting/colours/fill add nothing to the primary purpose of data handling - use simple elementary bespoke UserForm interface - use code instead of formula that will only alter the relevant data point.
Regrettably at this point I am unable to post code or files as some of us are experiencing problems with the site - I have attempted several time to reply to your post with regard to date/currency formatting.
As stated at the beginning, please do not take this as criticism, we have all been on 'learning curves' some of us longer than others.

O.K. I will step back - I have obviously offended - with 50+ years of experience I was only trying to save you the pain of hitting your head against the wall.

Hello 75Rupert:
There is a lot of code in the workbook. Is there a particular macro/process that's running slower than the rest that we could start with ?

If this is your first project it is very ambitious

And by-the-way ... I think you should show Torachan a little more respect. The experts here have a serious amount of knowledge and give it away here for free.
Your code , though ambitious has many brittle areas that could easily cause issues in the future

@75Rupert, no offence taken, we are here to help, at times the 'email' type medium is not the best communications platform and mistranslations of points of view can be obtuse.
However it can be equally frustrating to us when despite well meaning advice backed up by vast experience is discounted as an inconvenience.
I wish you well in your endeavours.

I understand completely if I do not get any help.

Hello 75Rupert:

I have had my tone misinterpreted while posting so I empathize with you and torachan
I am certainly willing to help you with your coding issues. If you could help me out and indicate which macro/process is effecting you the most I would start by taking a look at that for you.

.... You mention the filters ... is there a specific macro that activates these ??

Hello 75Rupert
While starting to look at the filter code I noticed you reference the "interface" sheet as "sheet1" (ie. reference sheet by Code Number vs Sheet Name).
I know this is a common practice but thought I should warn you about how fragile / unpredictable the code number can be.

FOR EXAMPLE ...
Here's screen shot of initial code number of "interface" sheet where the code number = 1
ScrShotPreMove.gif

then a user attempts to make a backup copy of sheet but forget to checkMark "copy" (common mistake)
ScrShotCopy.gif

.... after realizing mistake user quickly "move" sheet back to the original file (common solution to common mistake)
.... and look at how the code number of "Interface" has changed ( ie. code number = 10 now)

ScrShotPostMove.gif

RESULT
You now have code that will crash big time. And you will need to update your code in 18 places.
This move/copy mistake does not affect the sheetName , only the code number.
So referencing the sheet name would not have been as brittle.

This is only 1 scenario of many.
I had one scenario , involving code number reference , cost a retail client of mine over $100,000.
An accountant wrote a macro involving sheet code number that ending up looking at backups of the pricing sheet that was very old .
The company was releasing cost/pricing numbers to there over 300 retail stores on data that was way out of date.... a scenario that would NOT have happened if referenced sheets with the sheet name.

Nothing is foolproof. Generally codenames are better - but you should alter them from meaningless things like Sheet1.

Nothing is foolproof. Generally codenames are better

No sheet code number are not better. ... in fact they're horrible
When they break, (as you've said nothing if foolproof), they either (1) require coding experience to fix (2) or worse you don't know they're broken and code is referencing the wrong sheet.
When the sheet name breaks it requires no coding experience to fix and never points to the wrong sheet.

In my 20+ years of vba consulting I have had 0 occurances of having to fix a break because of a sheet referenced by name.
However I've had numerous calls to fix code that used sheets code number. One in which the client had lost over $100,000 due to the mistake.

I've heard arguments about this allows the sheet name to be changed on the fly as a great advantage.
But to me that's one of the dangers. Macro's can easily end up pointing at the wrong sheet and keep processing.
That's the scenario that cost my client $100,000 .. the macros for stock pricing updates were looking at old pricing sheet eg. "pricing backup 2020" instead of a sheet called "pricing"

I can also write a couple of lines of code to check if sheet "pricing" exists and tell user if not found. (see my next post for example)
How do you write code to message user that "sheet1" is missing ... and how do they fix the problem ??

YOU ARE CORRECT ... NOTHING IS FOOLPROOF.
So when it does break wouldn't you like it to be something obvious and fixable by the user ?
Eg. msgbox saying "Missing sheet pricing" ... so user adds back the sheet
vs
msgbox saying "missing sheet sheet13" ... and having to call in someone with coding experience
... or worse ... the code just keeps on working on wrong sheet

User's Message when using tab name as reference...

scrShotMsg01.gif

User's Message when using code number as reference ...

scrShotMsg02.gif


I've never found a scenario where I decided code number was a wiser way to go .

Hello,

I apologize for posting this here, but this website will not allow me to post a new message nor send an email.

I have been trying to post a message to the vba Forum for the past 3 days. I thought the problem might be that the website is migrating to a new server however I see others are posting.


After I compose a message and hit submit, I get a message on my browser that says "This page isn't working" or "Excelforum is currently unable to handle this request." Http Error 500.

Can you help?

Thank you.

No sheet code number are not better ... CONTINUED

Method of testing if sheet exists in workbook.
If found continue with macro.
If not found alert user and exit macro.
Note the re-usable function

Please Login or Register  to view this content.

Hello 75Rupert
No there is no missing sheet , sorry , this was just a follow-up to discussion with rorya on using code numbers vs names.
However my last code example is something I use in ALL my macros that reference a sheet.
Eventually a user will rename or delete a sheet and this handles the problem effectively instead of the macro just crashing.

All code/macros should have effective error handling for common problems , sheet deletion/rename is a common reason for errors in macros.
Some people try to skirt the issue , instead of handling it , by using sheet code numbers , which can actually cause bigger problems. (see last post for discussion)


Method of testing if sheet exists in workbook.
If found continue with macro.
If not found alert user and exit macro.
Note the re-usable function

Please Login or Register  to view this content.

Hello Rupert:
I'm attaching the first rough draft of code for copiing invoice info from summary sheet to interface sheet.
Now this is rough in that it has lots of hardcoded references ... but I would be interested in you testing in a bulk data situation and see if it helps... before refining

As requested this solution uses arrays for the transfer of data from 1 sheet to another.

Please Login or Register  to view this content.

Originally Posted by nimrod1313

No sheet code number are not better. ... in fact they're horrible

That's your opinion.

When they break, (as you've said nothing if foolproof), they either (1) require coding experience to fix (2) or worse you don't know they're broken and code is referencing the wrong sheet.

They are less likely to break if used well, and the same applies to tab names (which are far more likely to be changed by a user.

When the sheet name breaks it requires no coding experience to fix and never points to the wrong sheet.

Never? Really?

In my 20+ years of vba consulting I have had 0 occurances of having to fix a break because of a sheet referenced by name.
However I've had numerous calls to fix code that used sheets code number. One in which the client had lost over $100,000 due to the mistake.

And my experience has been the contrary. Users often change sheet names they decide they don't like.

I've heard arguments about this allows the sheet name to be changed on the fly as a great advantage.
But to me that's one of the dangers. Macro's can easily end up pointing at the wrong sheet and keep processing.

Seems to me this contradicts your argument? Unless you're suggesting that users should be able to swap sheet names around so the code automatically processes a different sheet, in which case you have an odd model.

[quote]I can also write a couple of lines of code to check if sheet "pricing" exists and tell user if not found. (see my next post for example)
How do you write code to message user that "sheet1" is missing ...[quote]

Same.

and how do they fix the problem ??

They don't. I wouldn't expect them to fix a serious error with the model, any more than I would expect them to fix a code glitch. If a user has removed a sheet that is integral to the model, why would I trust them to fix that correctly? That's why they pay me - support is included.

As I said at the start, nothing is foolproof. Far better programmers than I recommend using codenames when appropriate (it's not always practical) and I have not had issues adopting that approach. I have had many instances where users rename worksheets and want to keep their new names and they are, the client, so...

As with all things, your mileage may vary and you're entitled to do it your way. There are those who don't believe in declaring variables, or using meaningful names for anything, or unit testing, and if that works for them, I have no issue with it (as long as I never have to pick up their work).