PDA

View Full Version : Help please - very new to this



pominoz
05-06-2013, 05:52 PM
Hi there,

I am very new to VBA, I am trying to create a spreadsheet that I can use to track my teams targets and actual sales for each financial year, Quarter, Month, Quarter To Date totals, Month To Date totals and Year To Date totals.

I would like to be able to open the spreadsheet and be presented with an options screen, which would include: 'Input targets', 'Input actuals', 'End Month', 'End Quarter', 'End Financial Year'

The 'Input targets' option would allow me to enter the consultant, the Financial Year and the Month which would then automatically calculate the appropriate Quarter. I could then enter the contract sales target, contract running target, and perm sales target for that month. This data should then update the following report sheets: 'Month to date', 'Quarter to date', 'Year to date'.

I could then select 'Input actuals', which would allow me to enter the consultant, the Financial Year and the Month which would also automatically calculate the appropriate quarter. i could then enter the Actual Contract sales, Actual Contracts running, Actual Perm Sales. This data should then update the following report sheets: 'Month to date', 'Quarter to date', 'Year to date'.

I appreciate that this sounds quite complicated but I am hoping there is a relatively simple solution using VBA forms or input boxes.

Any and all help would be very gratefully received.

Thanks,

pominoz :dunno

SamT
05-07-2013, 06:14 PM
pominoz,

Welcome to VBA Express, I hope your visits here are very helpful.

As you are beginner to VBA, allow me to wax philosophical about coding in VBA. By a very large margin, VBA code is relatively short and sweet, and CPU and Memory restrictions just aren't an issue. For that reason, I suggest that all your code be as easy to understand as possible even when the verbosity adds to both CPU and Memory usage. The reason for this recommendation is that you are the person who must understand the code when circumstances change and it must be modified. This may be a significant length of time after you first start using it.

There are really only two basic types of coders, Organic and Structured. The Organic coder starts with the smallest bits of data and code to required to get "Something Done." Then he adds a a bit more data and code and gets the two sections to work together. After repeating this cycle a few times, he has something that he cannot make work. It's too organic.

The Structured Programmer arranges all his data until it is as concise, relevant, and intuitively obvious as possible. As he is doing this repeated (re)arranging, he considers how his code will use the data. Only then will he start to write any code.

Concise data merely means that there is no duplication of data anywhere in the entire set of data; Relevant means that there are no calculated values stored with the data. An intuitive data table's structure is immediately obvious to any person.

You have described several sets of data, Goals or targets, and actual sales. Contracts, new, existing, and finalized. And, Team membership.

You have also described several Reports or mathematical functions, basically, all the data types in various combinations by various dates and periods. Plan on storing reports in a separate folder from the Data.

You did not say if your financial year coincided with the calendar year, but, IMO, it is always best to store data in calendar year notation and only convert it for reports. It is trivial to convert CY notation to FY notation, but it is difficult to perform the math on FY notation.

All similar data should be kept in the same workbook, and if your data structure is not too large, you can keep it all in the same workbook. I would consider the following data books; Contracts, Team membership, Targets, and Sales. The three similarities are respectively, Static, Synthetic, and Active.

The purpose of the Contract and Team data tables is not only to provide all the information you want, but also to give VBA a cross reference ID number that is consistent across all data tables. They are considered Static since they rarely change, When a contract is sold and when it ends, and when a team member joins or leaves the team.

I would suggest that the first five columns of the Contracts Data Book, Sheet 1 be; ID, Name, Sales Date, Term Date, and Amount. The next sheet should be Contract ID, Consultant ID, Date Contract transferred, Consultant Transferred to ID. Any other sheets pertaining to an individual Contract should have the Tab name the same as the Contract ID, with a standard prefix, con, con_, c_ or even contract_.) The prefix is required to prevent duplicate names.

That the first columns of the Team Data Book, Sheet1, be; Id, Name, Start Date, and End Date. That Sheet2 have the Consultant ID in Column "A" and the remaining columns contain the Contract Id's that consultant handles. Again, the performance sheets for the consultants should be tab named with the consultants ID with a standard prefix, (t_, team_, p_, per-, m_, tmem_, or even consultant_,) as long as it isn't the same as another Data's ID prefix.

All other references to consultants and Contracts should be by ID. This will greatly facilitate coding and future changes. Since all entries are by UserForm and VBA code and all reports are by code, this will not cause a problem for us humans.

All column labels should be human readable, except that all spaces should be deleted or replaced with an underscore, (FirstName or First_Name,) must not start with a number and should not have any punctuation.

It will be very handy for everybody who gets involved in this project if you develop a workbook of report templates. Each Template should be fully formatted and ready to use except that: to the side of the template describe what it is used for; ("Gives the summary of all contracts"; " Shows the activity of one team member.)

In each cell that has a calculated value, put a cell name, ("Value 1", EOY total sales", etc.) to the side of the template list these cell names and tell how they are calculated, (EOY total sales = for Consultant ID# N, Sum from Book Sales, Sheet tmem_N, column Sales_to_Date for current QTR.)

See ya soon :devil2:

pominoz
05-07-2013, 06:25 PM
Hi There,

Thanks for your reply.

I have posted a follow up to this question that hopefully makes things a little more clear.

http://www.vbaexpress.com/forum/showthread.php?t=46189

Please let me know if this helps to explain things better.

Pominoz

SamT
05-08-2013, 08:23 AM
I see that you are an Organic developer.