This project will utilize several of the VBA programming components discussed in this chapter. The project contains several different examples of data types including integer, floating point, string, and date types. I introduce some new functions designed to work with the date and string data types. The project also demonstrates nesting functions, the use of constants, and some simple mathematical operations.
The majority of the work for this project will be handled by the Excel application via formulas and a chart. The requirements handled by the VBA program will be limited to collecting the user's name and birth date, and outputting the result of some date calculations. As was the case for the Colorful Stats project in Chapter 1, there is nothing in this project that could not be accomplished in the Excel application without the aid of a VBA program.
Nonetheless, I will show you how to build a fun little project that you can use daily to track the status of your biorhythms.
Your biorhythms (if you believe in them) are on sinusoidal cycles that vary in length for the three types. The lengths of the cycles are 23, 28, and 33 days for your physical, emotional, and intellectual cycles, respectively; with each cycle type starting on your birth date. Your best days are supposedly in the first half of a cycle when the sinusoidal curve is positive. Likewise, your worst days are in the second half of a cycle when the curve is negative. Critical days are said to be when you cross the boundary between positive and negative days.
I don't have a lot of faith in biorhythms, but they are fun to calculate and examine; and if you are having a bad day when your biorhythms are negative or critical, it gives you something to blame it on.
Requirements for Biorhythms and the Time of Your Life
As mentioned earlier, I've left most of the work to the Excel application by using formulas to calculate the sinusoidal curves for the three cycles, and a chart to display the curves. The specific requirements of the project follow:
1. The biorhythm spreadsheet shall use formulas to calculate a sinusoidal curve for each of the three cycle types. Note that these three curves are static.
2. The spreadsheet shall contain an embedded chart that displays the static curves described in requirement 1.
3. The VBA program shall be initiated from a Command Button control added to the spreadsheet.
4. The program shall ask the user for his or her name and birth date.
5. The program shall output the user's name (formatted in proper case) and birth date (formatted as Month, Day, Year, Weekday) to the spreadsheet.
6. The program shall calculate the user's age in years, months, days, hours, minutes, and seconds and output the results to the spreadsheet.
7. The program shall calculate the current position of the user's biorhythms in each of the three cycles (day and magnitude) and output the results to the spreadsheet.
8. The embedded chart on the spreadsheet shall contain a data series for each of the values calculated in the previous requirement.
The user interface for the project consists of a single Excel worksheet containing the data for the static sinusoidal curves, an embedded chart, and a Command Button control for initiating the program. The worksheet is preformatted to make the data presentable. The scatter chart contains six different data series that include the three static curves (column A has the x-values and columns B through D the y-values) and the values for the current status of the user's biorhythms (not visible until the program has been run). I will enter the VBA program within the Click() event procedure of the Command Button control on the worksheet so the user can initiate it with a simple mouse click. Figure 2.10 shows the Biorhythms and the Time of Your Life spreadsheet prior to executing the program.
The program requires the user to input his or her name and birth date. I will use an InputBox() function to collect this input and store it in a program variable. Next, the program will calculate the user's age in the different units (specified in the requirement list) and output the results to cells G30 through G35. Other outputs include the user's birth date to cells G29 and H29 formatted in a long form (month, day, year, weekday) and the current day and magnitude for each of the user's biorhythm cycles (cells A38 through A40 for the days, cells B38, C39, and D40 for the magnitudes). The calculation of the user's current biorhythms is based on his/her birth date and the number of 23, 28, or 33 day periods that have passed since he or she was born. Once the program has output the results to the worksheet, the chart is automatically updated by Excel.
The Biorhythms and the Time of Your Life spreadsheet.
The Biorhythms and the Time of Your Life spreadsheet.
Coding Biorhythms and the Time of Your Life
I have entered the following code to the object module for the Biorhythms and the Time of Your Life worksheet shown in Figure 2.10.
Private Sub cmdCalculate_Click() Dim userName As String
Dim yrPassed As Single, moPassed As Single, dayPassed As Single
Dim hrPassed As Single, minPassed As Single, secPassed As Single
Dim userBday As Date, curDate As Date
Dim bDate As String, bMonth As String
Dim bDay As Integer, bYear As Integer
Const SECSPERMIN = 60, MINSPERHOUR = 60
Const HOURSPERDAY = 24, DAYSPERYEAR = 365.25
Const PHYSICAL = 23, EMOTIONAL = 28, INTELLECTUAL = 33
Const PI = 3.14159265358979
'Get the user's name and birth date.
userName = LCase(InputBox("What is your name?", "Name"))
userBday = DateValue(InputBox("When is your birthday? (month/day/year)", "Birth Date"))
'Calculate length of life in different units.
curDate = Now 'Gets current time and date. secPassed = DateDiff("s", userBday, curDate) minPassed = secPassed / SECSPERMIN hrPassed = minPassed / MINSPERHOUR dayPassed = hrPassed / HOURSPERDAY yrPassed = dayPassed / DAYSPERYEAR moPassed = yrPassed * 12
'Get user's birthday in proper format.
bDate = Format(userBday, "dddd") bMonth = Format(userBday, "mmmm") bDay = Day(userBday) bYear = Year(userBday)
'Format user's name.
userName = StrConv(userName, vbProperCase)
'Enter time values into appropriate cells in worksheet.
Range("G28").Value = Trim(Left(userName, InStr(1, userName, " "))) Range("H28").Value = Trim(Right(userName, Len(userName) - Len(Range("G28").Value))) Range("G29").Value = bMonth & " " & Str(bDay) Range("H29").Value = bYear & " (" & bDate & ")" Range("G30").Value = yrPassed Range("G31").Value = moPassed Range("G32").Value = dayPassed Range("G33").Value = hrPassed Range("G34").Value = minPassed Range("G35").Value = secPassed
'Formula for day of cycle.
Range("A38").Value = (Range("G32").Value / PHYSICAL - _
Int(Range("G32").Value / PHYSICAL)) * PHYSICAL Range("A39").Value = (Range("G32").Value / EMOTIONAL - _
Int(Range("G32").Value / EMOTIONAL)) * EMOTIONAL Range("A40").Value = (Range("G32").Value / INTELLECTUAL - _
Int(Range("G32").Value / INTELLECTUAL)) * INTELLECTUAL
'Formula for magnitude of biorhythym.
Range("B38").Value = Sin((Range("G32").Value / PHYSICAL - _
Int(Range("G32").Value / PHYSICAL)) * . PHYSICAL * 2 * PI / PHYSICAL) Range("C39").Value = Sin((Range("G32").Value / EMOTIONAL - _
Int(Range("G32").Value / EMOTIONAL)) * EMOTIONAL * 2 * PI / EMOTIONAL)
Range("D40").Value = Sin((Range("G32").Value / INTELLECTUAL - _
Int(Range("G32").Value / INTELLECTUAL)) * _ INTELLECTUAL * 2 * PI / INTELLECTUAL)
Variable declaration is required by adding Option Explicit to the general declarations section of the object module for the worksheet. All other code is added to the Click() event procedure of the Command Button control named cmdCalculate. Variables and constant declarations are placed at the top of the procedure. Date and string variables are used to hold and manipulate the name and birth date obtained from the user. Numerical variables are used to hold the various lengths of time the user has been alive and the numerical components of the user's birthday.
Input is gathered from the user with the InputBox() function. Notice that I placed the InputBox() function inside the parameter list of the LCase() function. This is called nesting functions. In nested functions, the innermost function runs first; in this case, InputBox(), then whatever the user enters in the input box is passed to the next function, LCase(). The string entered by the user is then stored in the userName variable with all characters lower case. Another InputBox() function is used to retrieve the user's birthday. Again the InputBox() is nested in another function. The DateValue() function is passed a string parameter representing a date and is used to convert the string to a value of type date. The date is then store in the variable userBday.
Now you must process the information obtained from the user. First, I get the current date and time from the operating system by using the Now function and store it in the date variable curDate. The Now function is somewhat unusual in that it does not take any parameters. The curDate and userBday variables are passed to the DateDiff() function along with the single character string "s". The DateDiff() function calculates the difference between two dates in the interval specified, in this case "s" for seconds. Once the user's life in seconds is known, it's a simple matter to convert this number to minutes, hours, days, months, and years using the constants defined earlier.
The DateDiff() function returns a value of type variant (Long). This means that the function will return a long integer unless the value exceeds its range (2,147,483,647), in which case it will promote the return value to the next largest data type with integer values. In the Biorhythms and the Time of Your Life program, the range of the long data type will be exceeded by anyone more than 68 years old. Thus, to avoid a possible data-type error, the variable secPassed was declared as a single data type. This ensures the value from DateDiff() will be within the variable's allowed range of values. I did not want a floating-point number for the value of secPassed, but I don't need to be concerned because I know the DateDiff() function will only return a whole number.
The Format() function can be used with numerical, string, and date data. Here Format() is used to return the weekday the user was born, and the month as text rather than the numerical representation. The dates are passed as variables along with format strings ("dddd" and "mmmm"). These strings tell the function what format to use on the return value. For example, "dd" would return the numerical value for the day of the month, and "ddd" would return the three-letter abbreviation.
Next, the Day() and Year() functions are used to return the day of the month and year as integers and the StrConv() function converts the user's name to proper case (first letter of each name is capitalized).
Now that the time of life values have been calculated and the user's name and birth date formatted as desired, they are output to the appropriate cells in the worksheet. The only new element here is the Str() function which converts a numerical value to a string data type. The Str() function is not really needed for the conversion in this case. Since the & is used as the string concatenation operator, VBA assumes I want the variable bDay treated as if it were a string when the Str() function is omitted. If + is used as the string concatenation operator, then the Str() function must be used to avoid a type mismatch error. For clarity, I recommend using Str() in examples like this even when using the &.
The converse of the Str() function is the Val() function. The Val() function is used to convert string data to numerical data.
The last part of the program calculates and outputs the user's current day and magnitude for each of his/her biorhythm cycles. The current status of the user's cycle is calculated using the number of days he or she has been alive (from cell G32) and the length of each cycle. The Int() function is used to return the integer portion of a floating point number and the sin() function calculates the sine of the value passed to it. Note the use of the line continuation characters in the code.
That concludes this chapter's project. Although it's not exactly a long program, you may be feeling a bit overwhelmed by the number of functions used. Don't worry about learning all the functions available in VBA and how to use them—you can't! There are way too many, so it's a waste of time to try to memorize them all. I am familiar with the string functions, because I use them quite often, although I still had to look up syntax and parameter lists a couple of times while writing this project. The date functions are another matter. I didn't know any of the date functions before writing this program. What I did know is the essence of how a function works. I also realized that VBA was very likely to have a number of functions that worked on the date data type. Then it was a simple matter of searching the on-line help and looking at my choices.
Was this article helpful?