Help optimising my code

Hi all,

I have a macro-enabled workbook which has a user interface panel (main spreadsheet) and what can essentially be called a data bank (timetable spreadsheet). This is for my science department in our school. I designed it so that there are 4 macros that help us quickly and efficiently put pupils into detention, as well as see which pupils are due for detention on what days. It also lets us see which teacher (by referencing the teaching code) has the next supervision, as well as what dates they are due to supervise over the course of the whole year.

The only 4 macros in the main spreadsheet are as follows:

1. Add_pupil
2. Mark_pupil_present
3. Remove_pupil
4. Reset_date

That last one is very simple and only sets the date in cell E3 to today's date, should the supervising teacher quickly need to check who is in detention on that particular day.

So let me briefly explain how my macros function to (hopefully) help save you guys some time in answering my question:

Add_pupil - If pupil name (cell H3) is blank, gives a warning, otherwise goes along every column value in the 4th row on the timetable spreadsheet and compares the date in cell E3. Once the date matches, it then scrolls down that column and finds the next blank cell, and inserts a string composed of the teacher code (cell B2) & pupil name (cell H3). Once done, it then ends the macro (to prevent repeats).

Mark_pupil_present - The selected cell must fall between E7 and E26, otherwise an error message is thrown. Also, if a cell within this range is selected but there is no pupil present (i.e. there is just a "-" present) then it gives another appropriate warning. Otherwise, similarly as above, it goes along all the columns in the timetable spreadsheet, checking the value in the 4th row, until the date matches the date in cell E3. Then it scrolls through the column, finding the cell that has the same value as the selected cell (basically string matching) and then proceeds to append a string saying "PRESENT". Once done, it then ends the macro (to prevent repeats).

This then changes the cell to a green colour (conditional formatting, NOT part of the macro).

Remove_pupil - Identical search pattern as in Mark_pupil_present, but instead of appending a string, it simply clears the cell contents.

I was wondering if you guys could help me optimise my code; I'm by no means a VBA guru and the majority of what I know I've only learnt by copying macros from other forums (and then learning how it works by testing it out and modifying the code), so there could be a wealth of functions I am not aware of that probably do what I want even faster. I have heard of a search function that exists in VBA although I haven't read into it enough as of yet.

Many thanks for your help in advance and I look forward to seeing how I can improve my workbook!

Hi all,

if anyone could help me with this, I'd greatly appreciate it. The macros are not very large (around 20 lines or so each), in case you're expecting massive algorithms :P

Hi there,

I've structured your code a bit better (I think!), so take a look at the attached workbook and see if it does what you need.

The most significant changes are the use of Constants, Variables and Functions to significantly reduce the amount of repetition in your code, and thereby reduce the number of locations where changes may be required in the future.

The code used is as follows:

Please Login or Register  to view this content.

The Red values may be changed to suit your own requirements.

The Blue values relate to worksheet-level Defined Names which will make life easier for you if you need to alter the layout of the worksheets. You won't need to amend the code if you change the location of (e.g.) the Teacher cell - just use Excel's "Move" command, and the Defined Name associated with that cell will automatically reflect the new location of that cell.


Hope the above helps - please let me know how you get on.

Regards,

Greg M

Originally Posted by Greg M

Hi there,

I've structured your code a bit better (I think!), so take a look at the attached workbook and see if it does what you need.

The most significant changes are the use of Constants, Variables and Functions to significantly reduce the amount of repetition in your code, and thereby reduce the number of locations where changes may be required in the future.

....

Hope the above helps - please let me know how you get on.

Regards,

Greg M

Thank you very much for your help! I've had a look through your code and I get the gist of how your macros work, but I am confused. Your macros have a significant increase in code compared to my macros, so what are the advantages?

As you mention at the beginning, if I changed my spreadsheet I would have to modify my code somewhat but I didn't think it would be that of a hassle, unless I'm missing something?

Thanks in advance,

Daniel

Hi again Daniel,

One thing about coding is that "shorter" doesn't always mean "better"!

You'll remember that in your original code you had several comments reminding you to modify your code if the layout of the main worksheet ever changed. The more you can arrange your code to eliminate or at least minimise the need to do this, the better.

You should also try to arrange your code so that values which normally do not change (e.g. the row number of the first data row on a worksheet) are specified in ONE location only - this means that if you ever need to add extra header rows etc. above your data, the row number of the first data row needs to be changed in only one location. I usually try to define all such values as Constants located at the start of a routine, so it's fairly easy to see where changes need to be made.

As you've obviously spent a lot of time in developing your application you probably think that every detail of your code has been permanently engraved on your brain, but nothing could be further from the truth! When you revisit your code after several weeks/months you'll be amazed at just how unfamiliar it looks, and well structured code is very helpful in ensuring that you don't have to start from Square One in trying to understand/remember how it works.

Hope this helps.

Regards,

Greg M