MS Excel Page


Microsoft Excel along with VBA Programming is a very powerful tool to automate a number of tasks we do in our daily routines. A number of data analysis, data maintenance and manipulations, report generation jobs in excel can be made easy and You can build some really cool stuffs using Excel features and VBA Programming to create macros. Learning Excel and VBA programming is probably the next easiest thing to do as You find a crowd of online resources on the web.  Personally I learnt most of the basics from the following two sites:
Also You can download the Primer in Excel VBA programming right from here and keep it handy. Its a very good reference point and a study material. (Click here to Download)

I am a big fan of Excel and VBA Programming and have developed a number of small utilities most of them related to my project work. You may these sheets directly as they are or customize them according to your needs, but but the code embedded in these sheets can definitely be a source of reference to develop Your own utilities as per Your need. Click on the download link to download the Excel workbook.

1. Split Costs (Click to download Workbook)


Its a ready to use utility to split the cost incurred during a trip or event where the total amount is shared by more than one person. Its a sort of account maintenance tool where the total expenditure is shared by a group of people either for a event or for a particular period of time. Typical uses are Trip or picnic expense management, Mess Expense Management, Party expense management etc..
  1. People who are involved in sharing the expense have to be added in the Names Master Sheet. At most 10 people are supported per event in the cost splitting process.
  2. Fill in the Expense Entry sheet about the Amount, Date, Who made the payment and who all are involved in Sharing for that Item. The formulas in the sheet splits the amount for that item to equal parts according to the number of people. If the split is not supposed to be equal, then You have to unprotect the sheet(Use password: one490942 to unprotect the sheet) and override the formula to hardcode the value in the corresponding cell. Please note that this kills the formula in that particular cell.
  3. Now You click the Process Data button on the sheet and it does all the calculations for You. The final settlement and details of calculation are in the Calculations sheet in the workbook.
  4. At any time You can use the Reset Calculation Sheet and Reset Entry sheet buttons in the Entry Sheet.



2. Weekly Status Reports for IT Projects (Click to Download Workbook)


The Weekly Status Reporting Tool is a standard Status Update Report which resources fill at the end of the week to summarize the tasks assigned and worked upon in the week. The resources fill up the Status report sheet and the Billed Hours sheet and the manager is responsible for the control sheet maintenance and use. The Workbook should ideally be placed in a common drive and resources should fill it up one at a time. Various useful analysis reports can be generated by the tool which aid in team performance analysis and making correct forcasting and assignment decision.                                                              
Instructions:                                                               
  1. Enable Macros While Filling Up the Sheet.   
  2. Fill Up the Forcasted/Claimed Hours in the Billed Hours Sheet.
  3. The Resources and Applications have to be set up the the administrator in the Control Sheet.
  4. Make sure the Report Date is filled up in the B1 cell of status report sheet.    
  5. Various Columns of status report sheet has different validations. Make sure You follow the rule while filling up the sheet.
  6. The control sheet is password protected and can be viewed only the the administrator(Project Manager in this case). To go to the Control Sheet, click on the button on Billed Hours sheet. The initial password is set as "HelloWorld". Please change the password in the Control Sheet.
  7. There are five different reports generated from the status report sheet. Just click on the corresponding report buttons and it takes You to the corresponding report. Please make sure You have clicked on the Refresh Data Button to get the correct reports.
      • Application Wise workload distribution for the current week.                                                            
      • Application wise Ticket Severity Analysis.                                                            
      • Tasks identified by the tool as "Out of Schedule" or "Potentially items of Efforts variance from estimates". The sheet does the calculation based on Report Date, Task Start Date, Task End Date, Estimated Effort, Actual Efforts till Date, and %Complete.                                                            
      • Resource Wise Productivity. This can give us a indication of Forcasted Work, Distribution of Work and help resourcing and staffing decisions along with solving issues if there is a area of problem.            
      • Report of remaining work per day of the current tasks for the resource which help in making future forcast and assignment of new tasks.                                                            



    3. Sending Emails from VBA Applications(Click to download Documentation)
    Many a times Your VBA application may need to send dynamically generated/static emails to various email addresses. These email address and data can be from the Workbook itself. Here are two approaches of sending out emails from Your VBA Applications.

    Post a Comment

     
    Support : Creating Website | Johny Template | Mas Template
    Copyright © 2011. Atom's Arena - All Rights Reserved
    Template Created by Creating Website Published by Mas Template
    Proudly powered by Blogger