Reference no: EM132835387

VBA Project

Download, open the P3_arAging.xlsm file and review the Accounts Receivable data it contains.

The deliverable of this assignment is to write an algorithm that reads the data on the worksheet and produces an A/R Aging Report and Summary. Your solution should execute when the user clicks the "Run Aging" button.

The algorithm should prompt the user to input the date to be used for determining the invoice's "Age". Write the selected date to the Excel worksheet, cell K2. Your code will then need to process each row of A/R data in the worksheet and calculate the number of elapsed days since the invoice date. [yes, date math works in VBA, just subtract one date from another]. This value establishes the invoice's 'age' (Aging Date - Invoice Date. Based upon the invoice's age, you will write the Invoice Amt (in column D) to the appropriate column. Invoices less than 31 days old should display in the Current column (column E), invoices less than 61 days old should display in the Over30 column (column F), etc.

Use a Do/While loop to process the data - and make sure that your algorithm will work whether there are 5 invoices or 1500. You can safely assume that you will never need to age more than 1500 invoices.

While processing each invoice, accumulate the invoice amount to sum each aging category and display the sums after processing all the data in cells K3 - K6. You may NOT use Excel's sum function - I want you to code the accumulation.

Create an efficiently coded solution that performs the aging determination and the accumulation in a single loop.

Run your solution a few times, with different dates, to test for any other features that you may need to add. Use the Aging Date of 3/10/2021 to check your totals against mine.

Note that I have provided you with a Reset Data button that copies the original data from a hidden worksheet to the P3 worksheet (in case your code changes data that it should not have).

Attachment:- VBA Project.rar