Code simplification (format and conditional formatting)

[dunnobe]

Hey all,

I finished my code on how to format my worksheet.

For the sake of efficiency and speed, is there a way to improve my code?
This is only a snippet of my bigger project...

Please Login or Register  to view this content.

The result can be found in the attachment. Just click the format button!

Thank you for your feedback!!!

[xlnitwit]

You have some unqualified references which are best avoided, some repetition of objects that can be reduced, and turning off screenupdating is usually faster

Please Login or Register  to view this content.

[Zer0Cool]

Is there a specific part of your code that takes longer than you feel it should? How long does the code take before making any changes to it?

As xlnitwit has posted screenupdating can help in general. They have also done some spring cleaning on the code.

I also notice you get the last row many times throughout your code instead of getting it once and storing it in a variable to reuse (You eventually do but after having used it multiple times prior).

You could also use constants for many of the repeated values you use in your code (ie: "10284031"). Constants can help speed up your code and make it easier to update when 1 value is used many times in your code.

Also how does your data work? You obviously update the file and thus need to remove formatting and re-apply it. Are you removing old data and replacing it with new data, appending new data to old data, etc?

However you are going about it, if the layout of the data doesnt change, you may consider instead setting things up so that as much of the formatting as possible stays in place and making it dynamically expand/collapse based on the data.

So for example it may be quicker to simply expand the ranges the conditional formats are applied to instead of removing all the conditional formats and recreating them again.

Even if this is new raw data being dumped onto a blank sheet, you could still have a template sheet that you copy, dump the info on, and expand some of the formatting to match the dumped data. In this fashion you can avoid things like applying the table, setting freeze panes, adjusting zoom level, setting alignment, etc.

Depending how you get the data into the destination, you may even be able to paste it matching destination formats. This would allow you to format the "template" sheet 1 time and then maintain the basic formats (like font, size, etc) everytime you use the template to make a new sheet and dump data from it.

[dunnobe]

Thanks xlnitwit. I'll pay more attention to with/end with.

You must spread some Reputation around before giving it to xlnitwit again.

Sorry man, still can't rep you

[dunnobe]

@Zer0cool

Yep, I turn on/off the screenupdating in my complete code.

What I basically do is:

1 - load an excel sheet with events (holidays etc.)
2 - load an excel sheet with time clock data
3 - Create a summary of those 2 datasheets.

With this tool, I wanted to provide a quick overlook on an employee's activities.
Before, these datasheets were printed out and checked manually. :p

Yep, good idea on the last row variable and I don't know much about const, but I'll read upon it.

The number of columns used is fixed (although could be made dynamic based on CountTime value, but the number of rows is dynamic and depends on the input of the first 2 datasheets.

Feel free to give me some advice or constructive feedback.

Best regards.

[Zer0Cool]

Ill review it see if we can come up with some ideas...

Off the top of my head, at a glance is more of what I pointed out. You have many opportunities to declare variables and constants to help cleanup your code and potentially speed it up.

In codeLoadData for example you use "ThisWorkbook." multiple times. If you set this to a workbook variable and/or combine it with "With" statements you can simplify your code.

Also, in declaring variables something like:

Please Login or Register  to view this content.

means title is a string type, Filt ends up not being given a type so it defaults to a variant. Not a huge deal but good to get in the habit of assigning variables the data type that best fits them.

You have some public constants declared already, but use them in some modules and not others. You also use a variable in some places instead of a constant.

Please Login or Register  to view this content.

So you stored this string which is static in a variant type variable instead of a string constant. Then in another routine you declare it again and set it to the same string again. Doing this as a public constant would make alot more sense.

In building out the data you clear the whole summary sheet then re-add the headers using a static array...why not just clear everything under the headers and leave them? You could also store all those headers in a string constant, seperated by comma's and then split them into an array.

Also no need to loop cells to dump an array into a range (for the headers). You can dump an array into a range of the same dimensions. Ill attach a sample of this method. Any time you can avoid looping cells, do so. Looping cells is very slow (maybe not noticeable in the headers, but in larger data sets it comes at a price)

Please Login or Register  to view this content.

In case it helps heres my code snippet for how I clear a table to reuse it:

Please Login or Register  to view this content.

I see loops going through cells and using find. If the same goal can be accomplished using filters, filter the data to what you want then copy/paste the range of visible results as needed. Should be a good deal faster than find in a loop.

Another thing to point out.

If you have routines call others...lets say for example routine 1 calls routine 2 calls routine 3. If routine 1 has screen updating turned off, you do NOT need to do so for the routines it calls as its already off (same for calculation mode, events, etc).

[dunnobe]

Thumbs up for reviewing.

I'll try to work with variables and with/end with more, but I'm only learning my doing.
Heck, I just discovered that I applied formatting to my header row twice!

This is my first real project and I'm already very happy with the results.
The only VBA I ever did before was a userform to e-mail project.

Br,

Bram

[Zer0Cool]

We all start some place. One of my favorite things to do is look at code I wrote 1, 2, 5 years in the past. Alot of "wtf was I thinking?!?!". Then you update your code, learn some new stuff and get even better at it.

However if you really have an interest in learning VBA, I would recommend getting a book or watching some videos on the fundamentals of VBA. Variables and scope, error handling, object model, common algorithms, loops, etc.

Learning the proper ways to approach VBA coding is alot easier when you haven't developed bad habits.

So what I would recommend (and do myself) is plan your VBA project before writing any code. I make an outline bullet pointing the key aspects of what my macro needs to do, in a logical order it has to be done in.

For example:

1. Create a template sheet with headers/empty table row and formatting
2. Get data from x file into table
3. Set groupings
4. Expand conditional formats
5. etc...

Once I have an outline I start thinking about and declaring my variables. I start with global/public variables and constants, the ones I will use across my whole project. It might be things like workbook, worksheet, a specific range, strings, etc.

The 2 above steps set a pretty clear path from that point how I am going to write my code. I start to expand on my outline as I write the code to account for new considerations ideas, like looping, error handling something unexpected, etc.

Lastly one of the best things you can do to help yourself is save snippets of code someplace thats easy to get to them. I have snippets of code, like the table clearing code I gave you. I have the code in OneNote and I keep sample files demonstrating the code in a folder. Its pretty much a daily occurrence I go and pull something from my snippets/samples. I have well over 100 Excel sample files I have created so I do not need to relearn/re-write stuff I have already tested and used.

I even have in my personal workbook templates for a new sub routine and new function so I dont need to rewrite the commented "sections" in my code or the generic error handlers I use.