lexluther88
11-11-2006, 05:18 PM
Might be useful to open the VBA code/excel sheet to see what I'm talking about: download the attachment**
might need to copy and paste it in the URL:
www
.cs.uiuc.edu
/class/fa06/cs105/mps/fall06/mp5/
mp5_given.xls
(Sorry! put them all together, since my rating isnt high enough to paste Links)
then open the excel spreadsheet to see what I'm talking about^Since my rating isnt a 5..it won't let me upload links...
Okay so now what I want to do is the following:
To set the return value of a function, all you need to do is pretend the name of the function is a variable and assign it a value. To continue the above example of AddTwo, the function would have the following:
AddTwo = intFirst + intSecondWe will also be using a Select Case statement for this function. This type of expression allows the code to check a number of possible values against a single variable in a compact fashion. Remember that a Select Case has the following format:
Select Case <Variable>
Case <FirstCase>
' Do something for when <Variable> = <FirstCase>
Case <SecondCase>
' Do something for when <Variable> = <SecondCase>
Case Else
' Do something for when <Variable> didn't match anything
End Select
The function to write is called GetManufacturerRow() which takes in one String input, strManufacturer. The return value is of type Integer. Write the function so that it uses a Select Case statement that looks at the input strManufacturer and compares it to the various companies listed in the Voting tables in cells F5:F9 (i.e. "The Hershey Company") and returns the row number that matches up to the one for that manufacturer in the Voting table. If strManufacturer doesn't match the provided companies, the function should return 0.
Create the function GetManufacturerRow() with the desired inputs and return type. You can put this near the top of the VBA code below Option Explicit and the constant.
Write the the Select Case statement.
First specify the appropriate variable to do the Select Case with. Note that this variable is the one that will be compared to each of the following Case.
Write the Case so that the manufacturer name will be compared. Use the values directly from the spreadsheet by doing something like Range("F5").Value.
Set the return value by assigning the appropriate row number. To continue the example in the previous step, the value assigned would be 5.
Repeat the previous 2 steps for each of the manufacturers.
Add the Case Else in-case none of the manufacturers match and have the return value set to 0.
A simple temporary test would be to modify the select button to display a message box that contained the return value as well as the manufacturer name. For example, a possible message would be "The Hershey Company = 5". This can be done with the following line of code placed in cmdSelect_Click:
MsgBox Range("F5").Value & " = " & GetManufacturerRow(Range("F5").Value
I tried the following:
Function GetManufacturerRow(strManufacturer As Integer) As Integer
Select Case strManufacturer
Case Range("F5").Value = 5
Case Range("F5").Value = "The Hershey Company"
Case Range("F6").Value = 6
Case Range("F6").Value = "Mars, Incorporated"
Case Range("F7").Value = 7
Case Range("F7").Value = "M&M"
Case Range("F8").Value = 8
Case Range("F8").Value = "Pepsico"
Case Range("F9").Value = 9
Case Range("F9").Value = "Tootsie Roll Industries"
Case Else
Range("F5").Value = 0
Range("F6").Value = 0
Range("F7").Value = 0
Range("F8").Value = 0
Range("F9").Value = 0
End Select
End Function
But when I try the test message box it just says ERROR mismatch.
If anyone could just help me with the proper format for the function with a select case embeded into it..with the hershey company so I can see the proper pattern, I WOULD REALLY APPRECIATE IT..
might need to copy and paste it in the URL:
www
.cs.uiuc.edu
/class/fa06/cs105/mps/fall06/mp5/
mp5_given.xls
(Sorry! put them all together, since my rating isnt high enough to paste Links)
then open the excel spreadsheet to see what I'm talking about^Since my rating isnt a 5..it won't let me upload links...
Okay so now what I want to do is the following:
To set the return value of a function, all you need to do is pretend the name of the function is a variable and assign it a value. To continue the above example of AddTwo, the function would have the following:
AddTwo = intFirst + intSecondWe will also be using a Select Case statement for this function. This type of expression allows the code to check a number of possible values against a single variable in a compact fashion. Remember that a Select Case has the following format:
Select Case <Variable>
Case <FirstCase>
' Do something for when <Variable> = <FirstCase>
Case <SecondCase>
' Do something for when <Variable> = <SecondCase>
Case Else
' Do something for when <Variable> didn't match anything
End Select
The function to write is called GetManufacturerRow() which takes in one String input, strManufacturer. The return value is of type Integer. Write the function so that it uses a Select Case statement that looks at the input strManufacturer and compares it to the various companies listed in the Voting tables in cells F5:F9 (i.e. "The Hershey Company") and returns the row number that matches up to the one for that manufacturer in the Voting table. If strManufacturer doesn't match the provided companies, the function should return 0.
Create the function GetManufacturerRow() with the desired inputs and return type. You can put this near the top of the VBA code below Option Explicit and the constant.
Write the the Select Case statement.
First specify the appropriate variable to do the Select Case with. Note that this variable is the one that will be compared to each of the following Case.
Write the Case so that the manufacturer name will be compared. Use the values directly from the spreadsheet by doing something like Range("F5").Value.
Set the return value by assigning the appropriate row number. To continue the example in the previous step, the value assigned would be 5.
Repeat the previous 2 steps for each of the manufacturers.
Add the Case Else in-case none of the manufacturers match and have the return value set to 0.
A simple temporary test would be to modify the select button to display a message box that contained the return value as well as the manufacturer name. For example, a possible message would be "The Hershey Company = 5". This can be done with the following line of code placed in cmdSelect_Click:
MsgBox Range("F5").Value & " = " & GetManufacturerRow(Range("F5").Value
I tried the following:
Function GetManufacturerRow(strManufacturer As Integer) As Integer
Select Case strManufacturer
Case Range("F5").Value = 5
Case Range("F5").Value = "The Hershey Company"
Case Range("F6").Value = 6
Case Range("F6").Value = "Mars, Incorporated"
Case Range("F7").Value = 7
Case Range("F7").Value = "M&M"
Case Range("F8").Value = 8
Case Range("F8").Value = "Pepsico"
Case Range("F9").Value = 9
Case Range("F9").Value = "Tootsie Roll Industries"
Case Else
Range("F5").Value = 0
Range("F6").Value = 0
Range("F7").Value = 0
Range("F8").Value = 0
Range("F9").Value = 0
End Select
End Function
But when I try the test message box it just says ERROR mismatch.
If anyone could just help me with the proper format for the function with a select case embeded into it..with the hershey company so I can see the proper pattern, I WOULD REALLY APPRECIATE IT..