Solution - Novice Challenge 3: What Do I Owe You?
Challenge 3 was an interesting one. Not so much the challenge itself, but people seemed surprised at how little authors made in royalties. (This may come as a shock to some people, but most authors don’t make a lot of money off book sales.)
Be that as it may, those are the numbers we came up with: a 10% royalty for new authors and a 15% royalty for experienced authors. And here’s how we went about calculating those amounts in our spreadsheet:
Sub CalculateRoyalties()
Dim objRange As Range
Dim i As Integer
Dim iCount As Integer
Dim dPercent As Double
'Get the range of contiguous data
Set objRange = ActiveSheet.UsedRange
'Find out how many rows are in the range
iCount = objRange.Rows.Count
'Cycle through each row
For i = 2 To iCount
'Make sure the input is uppercase
'If N, give 10% royalty; if E, give 15%. Exit when we reach a blank cell.
If UCase(objRange.Cells(i, 1)) = "N" Then
dPercent = 0.1
ElseIf UCase(objRange.Cells(i, 1)) = "E" Then
dPercent = 0.15
Else
Exit Sub
End If
'Multiply the gross amount by the royalty.
objRange.Cells(i, 4) = objRange.Cells(i, 3) * dPercent
Next
End Sub
The first thing we do is declare a bunch of variables:
Dim objRange As Range
Dim i As Integer
Dim iCount As Integer
Dim dPercent As Double
Next we need to initialize a couple of those variables. We first retrieve a Range object that contains the contiguous range of cells in our spreadsheet – in other words, the UsedRange. We then use the Count property for the Rows object representing that range to find out how many rows we’ll be working with:
Set objRange = ActiveSheet.UsedRange
iCount = objRange.Rows.Count
Now that we have the preliminaries out of the way it’s time to get down to business. We have the range of rows we’re going to need to calculate, and we know how many rows there are. That means we can now head into a For loop that will loop us through all those rows:
For i = 2 To iCount
Notice that we’re looping from 2 to the number of rows (iCount). Why 2? Because we want to loop through every row that contains data, and, because we have a header in row 1, our calculations begin where the data begins, in row 2.
Next comes this If statement:
If UCase(objRange.Cells(i, 1)) = "N" Then
dPercent = 0.1
ElseIf UCase(objRange.Cells(i, 1)) = "E" Then
dPercent = 0.15
Else
Exit Sub
End If
This statement is checking to see whether the first column contains an N or an E. Let’s look at this one piece at a time. First we look at the value contained in the current row (row i) in column 1 (column A):
objRange.Cells(i, 1)
Then, just in case someone decides to enter the N or the E as a lowercase letter, we call the UCase function to turn the value in row i column 1 into an uppercase letter:
UCase(objRange.Cells(i, 1))
Now we simply check to see if the value is an N. If it is, we run this line of code:
dPercent = 0.1
A value of N means this is a New author, so his or her royalty percentage will be 10%. Therefore we set our percent variable (dPercent) to .10. We do the same thing in the ElseIf statement, this time checking for E (Experienced author) and setting the percent variable to .15 (15%). If we don’t find an N or an E, that means we’ve reached the end of our data and we can exit the subroutine:
ElseIf UCase(objRange.Cells(i, 1)) = "E" Then
dPercent = 0.15
Else
Exit Sub
All that’s left to do now is calculate the total and put it in the Royalties column:
objRange.Cells(i, 4) = objRange.Cells(i, 3) * dPercent
Here we’re taking the Sales amount (located in cell i, 3, which is the current row in the Sales column) and multiplying it by the percentage we just came up with (dPercent). We then assign the whole thing to objRange.Cells(i, 4), which represents the Royalties column in the current row.
And yes, we could have done this last step right inside the If statement. However, we put it outside that statement just to keep the logic separate and make the code a little easier for everyone to follow.
And that’s the end of Challenge 3. Here’s our final tally:
