Monday, 15 May 2017

Monthly Report Tutorial

At a former client, I was asked to submit monthly reports that show details of work performed in 15 minute increments.

My line of thought went something like this,

"Let's see, a monthly calendar, something like the one on my fridge door comes to mind and making one in Excel should be easy..."



One problem is space. If I do several tasks in one day, do I use tiny font to make the details fit, or do I make the calendar larger to the point that I have to scroll copiously?

Also, just how practical is that style of calendar going to be when it comes to adding up total time per task? Something along the lines of a regular timesheet would be better.

I can easily fit 32 rows on my laptop screen. That's a good start. So here's how to do the same thing I did, if you are interested.

Leave the first row for your headers. In cells A1 and B1, enter "Date" and "Day", then change the orientation. Right click the cells, select Format Cells, Alignment, and change Orientation to 90 degrees.



(You might want to change the Alignment too. Choose from the options on the Alignment Group on the Home Tab)

Enter the first day of the month in cell A2. Select range A2:A32, then change the format to either "d/m" or "m/d" as you prefer. Right click the cells, select Format Cells, Number, and enter the format in the Type text box in the Custom section.



Now enter =A2+1 into Range A3:A32 and click your Ctrl and Enter keys simultaneously to enter the formula into all selected cells.

In the same way, enter =CHOOSE(WEEKDAY(A2,1),"Su","Mo","Tu","We","Th","Fr","Sa") into Range B2:B32.

Adjust the width of both of these columns and set the alignment to suit.

You should have something like this.



And now for the details. Long descriptions take up space, so let's use numbers instead. Keep in mind that longer tasks won't be completed in 15 minutes, and recurring tasks will be duplicated so that's going to cut down the number of tasks in total. With any luck, we can keep things within double digits.

Start times allotted for the 15 minute intervals go in Row 1. Adjust the Orientation to 90 degrees. "h:mm" is a suitable format.



The task descriptions that match the numbers can go on the right. But note the numbers to their left to perform a lookup.



Important: adjust the following ranges to suit your requirements. Use Named Ranges if you prefer.

Enter formulas to add up the time. Type the following formula into Cell AI2, and drag down to the end of your list.

=IF(COUNTIF($C$2:$AE$32,AG2)=0,"",COUNTIF($C$2:$AE$32,AG2)/4)
You should have something like this.



You can freeze the first row if the number of tasks exceed the number of visible rows on your screen. (View Tab, Windows Group, Freeze Panes, Freeze Top Row)

Now for some extra features to enhance visibility. Why not add some Conditional Formatting to highlight the weekends? With Range A2:AE32 selected, click the Home Tab, Styles, Conditional Formatting, New Rule, then "Use a formula to determine which cells to format" and enter this formula. (Click the Format button to choose a suitable format)



Here's the result.



An ActiveX Combo Box and a bit more Conditional Formatting makes it easy to see when the work was done. If you can't see the Developer Tab on the Ribbon, select the File Tab, Options, Customize Ribbon, then tick "Developer" on the list to the right and click the OK button.



On the Developer Tab, select Insert from the Control Group to add an Active X Combo Box. (I've already added one to Cell AH1)



Right click the Combo Box and select Properties. Set the LinkedCell and ListFillRange properties. I've hard-coded my ListFillRange range reference but you can use Named Ranges too, as in "=Tasks" without the quotation marks.



When finished, toggle off Design Mode on the Developer Tab.

Note the linked cell. That gives me the selected item of the list. Now I use another formula to get the reference number which I have put in the cell below the linked cell (In this case, Cell AJ3).

=MATCH(AJ2,AH:AH,0)-1
If I select the first item on the Combo Box, Cell AJ3 will show 1.



Here's the Conditional Formatting for the details part of the report. (Range C2:AI32)



And here's the Conditional Formatting for the list. (Range AG2:AH32)



I also added some Data Bars to the hours.



And we're done.



No VBA was used so you can send the file without explaining the need to enable macros.

Here's a download link if you want to skip making one yourself.

Wednesday, 26 April 2017

AET VBE Tools v1.7.3

Yet another tool has been added!

You can now run a report that shows project statistics. It's a good way to get a general feel of how much work you have done, and where it is located.

Details include:
  • Module Names
  • Procedure Names
  • Count of Children (Project and Modules)
  • Procedure Scope
  • Count of Lines (Project, Modules and Procedures)
  • Count of Comments (Project, Modules and Procedures)
  • Count of Declaration Lines (Project and Modules)
  • Count of Declaration Comments (Project and Modules)



    Download the new version here.

    P.S. If you have purchased an earlier version, contact me by email and I'll send a new set of tools - free of charge!

  • Thursday, 6 April 2017

    AET VBE Tools v1.7.2

    Already? Didn't you just release v1.7.1?

    Yes, and I'm pleased to announce 2 new additions to the tools!

    Find Code In Project
    This tool creates a report that works in a similar way to the built-in Find dialog in the Visual Basic Editor.



    Details include,

  • Module Name
  • Procedure Name
  • Instances (count of code found)



    Select Current Procedure
    Do you hate scrolling?

    Selecting all code in a module is easy, just use the Ctrl + A keys on your keyboard. A procedure - not so easy, especially if it's a loooong procedure.

    Trust me, this tool helps. You and your hand(s?) can thank me later.

    Download the new version here.

    P.S. Tick the G+1 icon at the bottom of my post to tell me if you like what you see!

  • Monday, 27 March 2017

    AET VBE Tools 1.7.1

    It has been a busy couple of weeks, but I added some new features to my VBE tools.

    Line Continuation
    Here's a pic of what's available.



    My version of line continuation adds spaces and underscores after And and Or keywords.

    So something like
    If Left$(LTrim$(.Lines(lCodeLine, 1)), 4) = "Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 11) = "Public Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 9) = "Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 17) = "Private Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 16) = "Public Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Property " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Property " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Public Property " Then

    becomes
    If Left$(LTrim$(.Lines(lCodeLine, 1)), 4) = "Sub " Or _
    Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Sub " Or _
    Left$(LTrim$(.Lines(lCodeLine, 1)), 11) = "Public Sub " Or _
    Left$(LTrim$(.Lines(lCodeLine, 1)), 9) = "Function " Or _
    Left$(LTrim$(.Lines(lCodeLine, 1)), 17) = "Private Function " Or _
    Left$(LTrim$(.Lines(lCodeLine, 1)), 16) = "Public Function " Or _
    Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Property " Or _
    Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Property " Or _
    Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Public Property " Then


    Much easier to read. But there are 2 things to watch out for.

    1. If And or Or is between quotation marks, an error will occur. If this happens, click Undo last operation.

    The best way to check for errors is to use Compile VBA Project at the top of the Debug menu after adding line continuation.

    2. I haven't experienced problems myself, but I occasionally read about people whose code doesn't work properly if there is too much line continuation in a project. Something to keep this in mind anyway. Again, I recommend compilation of code and saving your work at regular intervals in case things go wrong.

    New Cleanup Project Options
    In addition to deleting excess blank lines (if you have several blank lines between code, only one will remain), you can now choose to delete all blank lines too. Not only that, you can delete comments and indenting.



    Personally, I like to have blank lines between regular code and indenting for readability, as well as comments to keep track of what is going on. But when the code is ready for distribution, the 3 new options can drastically reduce the size of your project which is definitely a good thing.

    Download the tools here.

    P.S. Tick the G+1 icon at the bottom of my post to tell me if you like what you see!

    Note: Thanks to everyone who participated in the Facebook sharing event. All good things come to an end, and the offer is now closed! (Don't forget to send your email address so I can send a copy of the tools. My email address is on the blog sidebar).

    Sunday, 12 March 2017

    AET VBE Tools v1.7

    Finally they're ready!

    What's new?

    1. Rename Userform Controls
    This tool allows you to batch rename all controls on a userform via a simple Find and Replace interface. In addition to changing the control names, it also changes code with the old control name to the new control name.

    So, if you want to change all controls that start with "CommandButton" to "btn" or "cb", etc, this is the way to do it all at the same time!



    2. Make Project Variables List
    The bigger the project, the more complex it becomes.

    This tool analyses your code and makes a list of variables and constants.



    Details include:
  • Module Name (Where the variable or constant is)
  • Scope (Public, Private, Type or Procedure)
  • Location (Declaration or procedure name)
  • Name (Variable or constant name)
  • Type (Variable or constant type, eg String, Long, etc)
  • Const (True or null, to discern between variables and constants)
  • Const Value (Value if constant)
  • Found in Project (Count within project)
  • Found in Report (Count within report)
  • Modules Count
  • Procedures Count
  • Unique Values
  • Duplicate Values

    More features are planned in the near future.

    3. Copy Code for Web
    If you need to show your code on the web, normal indenting won't show. The way to get around it is to convert indent spaces to " ".

    This tool does that and copies the code to the clipboard so you can paste it where required.

    4. Delete Orphan Code
    This feature was added at the request of Kellsens Willamos, who has supported me and the development of these tools from the beginning.

    What's "orphan code"?

    When developing, did you ever click userform controls by mistake? Maybe you get something like this.

    Private Sub lblSelectProject_Click()

    End Sub


    Chances are you don't want the code, and if there's nothing between the first and last line, it's a fair bet that you don't. The Delete Orphan Code tool looks for code like this and gets rid of it.

    I've added it as an option on Cleanup Project Code. To include it, tick the checkbox on the Settings form.



    AET VBE Tools are no longer free. But I'm keeping the price low so they are affordable. (And you will get future versions free of charge!)

    You can download them here.

    P.S. Tick the G+1 icon at the bottom of my post to tell me if you like what you see!

  •