PDA

View Full Version : [SOLVED] Help With Coding Structure



Rodger
11-26-2017, 07:19 AM
I've been stuck for a few days, trying to figure out how to best structure an Excel application for a friend. I'm relatively new to VBA and have been trying to teach myself by leveraging this, and other forums. Unfortunately, my employer discourages the use of Access, so I'm using this exercise to teach myself more Excel VBA.

The Goal: Create an application that a friend who is fairly computer illiterate can use, to track Client information for her physical training business. The application should allow the entry and updating over several variables including names, services, prices, start/end dates, starting/updated measurements and payments.

Progress: I've created forms that solve for the entry and updating of basic client information and measurements.

Dilemma: I am 100% stuck on how to best structure and code the application to track payments due and payments received, whilst factoring in changes to services selected, price points, payment frequencies and due dates.

I've attached what I've completed so far and am looking for advice on how to best solve for the aforementioned dilemma. Any and all constructive feedback is welcomed, even if it means completely restructuring the current design.

SamT
11-26-2017, 09:54 AM
What I would do
a Sheet for all members, past and present that contains all Member info, with one Column a Unique value
A Sheet for a list current members, with one Column the same Unique value as in the above
A sheet for financials, with one Column the same Unique value as in the above
Other sheets as desired, with one Column the same Unique value as in the above.
The Uniqe Key Columns on some sheets can be hidden and the member names used for User reference.

All code should use the Key Column (The Unique Value), Sheet Formulas, the MemberName column

All Sheets have a CodeName and a Name, (Tab Name.) I prefer to rename all sheet's CodeNames to something descriptive, and use that CodeName in all code.
MembersData
CurrentMembers
Financials
Schedule
Etc.

All Sheet Formulas use the Tab Name and If a User changes the Tab Name, Excel Edits all formulas to match. The User can not change the CodeNames. Also, I am a lazy typist, and

Financials.Range("A1")
'is shorter than
Worksheets("Financial Data").Range("A1")

Some things I like to use:
Enums for all Sheet Columns, by Label, (Header,) Value.

Enum FinancialsColumns
colKey = 1
colFirstName
colLastName
BlankColumn1
colPaymentAmount
colLAstPaymentDate
colTotalDue
BlankColumn2
'etc
End Enum

This allows me to code

With Financials.Columns(colFirstName)
Do something'
End With

And everything I, as the Coder, need to know, is right there on the Module.

Rodger
11-26-2017, 10:24 AM
Thank you for the reply Sam! These types of tips are things that will definitely help me increase the efficiency of the coding. I think where I'm struggling with your suggestions is A) having only 1 entry per Client, as I need to be able to compare progress from sign up through check ins, B) being able to account for changes in services signed up for (types and/or payment amounts and/or payment frequencies and C) how that better enables me to be able to auto-insert rows with next payments due.

SamT
11-26-2017, 12:21 PM
Financials would be a table with Money categories across and Members Down. It really should run for a year at a time with Existing Members and balances due carried to the next year's sheet.


s I need to be able to compare progress from sign up through check ins,
That depends on how progress is claimed. is it thru stages that can be listed across a sheet? Is it so complex that each member requires a separate sheet? :dunno:


being able to account for changes in services signed up for (types and/or payment amounts and/or payment frequencies Do clients sign up for more than one service type at a time? Then a Table with serice types across and current members down. If only one service at a time, then a "Service" Column in the Membership Sheet.

Payments and frequencies belong in a column on the Financials sheet.


how that better enables me to be able to auto-insert rows with next payments due.
Personally, I wouldn't do it that way. I would let the code calculate that from the Financials sheet.

I never use code to record anything that can always be extracted from a table, except when Printing, (an invoice or bill.)

Using Tables to save all data in makes it much easier to write code that uses that data. I always hide Data Table Sheets from the user and keep all sheets the user needs to see updated from the Tabular sheets with code.

Code manipulates Data. Users interpret mainipulated Data. Keep your data well organized from a code perspective. Present the Manipulated data in a way that makes most sense to a User.

In the future, when your friend's business gets too big for Excel, the DB coder will love the way your Raw Data is organized and it will save your friend beau coup money.

Any Business Application has at least three main concerns other than USer Friendliness:
Business Rules
Data Flow
And Data Points.

An example BR is "Add 10% to all Monies Past Due over 30 days.

A sample Data Flow is
Enter all New Membership Application information into a DB or Excel Table
Show User New Applications Report/Summary
User Approves/Disapproves Applictions
Add Approved Application Information to All_Members DB or Excel table
Add New Member Info to Current Members DB or Excel Table
Move Disapproved Applications to Circular file

Some Sample data points, All of which will wind up in one of at least three DB fields or Excel Table columns
MemerbshipApplicationMemberFirstName
MemberFinancialReportFirstName
MemberAddressMemberFirstName
EmploymentApplicationEmployeeFirstName
EmployeeFirstName
VendorReprentativeFirstName

Note that all DataPoints will have the very same Db Field or Excel Column Label: "FirstName"

DataPoints are discovered by researching all papers generated by the business. Thereafter, it's up to the App Designer to create the applicable Tables and Labels, generally making about 1/10 the number of Fields as there are Datapoints.

I know it seems like a lot of work, but organizing the Data Structure to suit the code makes the code creation process much faster and simpler.AND! It prevents creating Dead End Code that just can't handle the unorganized mess of data.

Failing to organize Data to suit code is probably the most common, and at the same time, the worst mistake new App Designers make

PS: DataPoint is my terminology for those things.

Rodger
11-27-2017, 04:00 PM
Thank you for the information! It's a lot to digest, but valuable. I'm going to take another look at the current structure, using your points and go from there.