How to : Program a MS Excel Form
If you are trying to make an excel form, chances are you lost at the many objects lying around on your form and are baffled as to how all the pieces fit together.
They key thing in writing a form is understanding how many objects are used and what minimum code must support each object and action. If you are lost and dont know what piece of code to write next, here are some simple guidelines I follow myself in programming my forms for performing engineering calculations.
1. Throw all objects you want on a form. Inputs and Outputs.
2. For an engineering calculation, if any input parameter changes, all outputs must vanish. Otherwise your form risks the possibility of displaying the incorrect value from a previous calculation before the current calculation is performed.
3. Initialize the form using built in “UserForm_Initialize()” function.
4. Initialisation of the form can be performed by a separate macro which pouplates the initial values to be displayed.
5. Use the most common three buttons as set in 6, 7 and 8
6. “Close” button unloads the form
7. “Clear” button can be used to invoke the initialization macro or clear the all input and output fields by making them blank.
8. “Calculate” button begins the calculation sequences.
9. When starting a calculation, first step is to check the inputs.
10. Check the inputs one by one. If inputs are not correct dont perform the calculation. If inputs are correct, read and interpret the inputs alongside.
11. Perform calculations. Use UDFs as necessary.
12. Estimate results. Diplay results as Label Captions.