Excel Case/else structure math operations.

**LadyB** · 03-13-2013, 06:19 PM

I am currently taking a class for Excel Vba, and have an assignment. I have tried everything I can think of, but can't get the calculations to work. In the AddUserInput procedure I am suppose to use a Select Case Structure to decide which math operation to use, also include a CASE ELSE to print an error message if an invalid math operation is entered by the user. I can get all of the inputboxes to show, but nothing happens and I don't know how to correctly format the Select case/case else structure. Any help would be greatly appreciated.

Option Explicit

Dim answer As Integer 
Dim num1 As Integer
Dim num2 as Integer
Dim Mathoperation As String

Private Sub Main()

    num1 = Val(InputBox("Please enter the first operand", "First operand"))
    num2 = Val(InputBox("Please enter the second operand", "Second operand"))
    Mathoperation = Val(InputBox("+,-,*, or / for math operation", "Math Operation"))

    Call AddUserInput(num1, num2)
    SendResult
End Sub 
Private Sub AddUserInput(num1 As Integer, num2 As Integer)
    answer = num1 + num2
End Sub  
Private Sub SendResult()
    MsgBox ("The answer is " & Str(answer))
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call Main
End Sub

**mehmetcik** · 03-13-2013, 06:40 PM

Hi and Good Luck.

Firstly as the main macro is defined as " Private" you can only call it from another macro. So not very useful.

Secondly you have messed up the line that defines the mathoperation, mathoperation will always be zero.
You should have used match or instr to convert the operation into a number.

Thirdly you are not passing the mathoperation value to the subroutine that actually does the maths.
Also why is it a subroutine?

Fourthly I think that you may have misunderstood the use of the case function.

I imagine that your tutor wants you to use the case function to carry ot a differrent operation on your variables based on the value of mathoperation.

The only error messages you need are 1: Numeric Input only for the two variables. For that use isnumeric() to check if numeric if not a simple message box to say numeric only
and loop back to the input box.

2: when you check for operation, + - * or / if responce is N/A or 0 then message box to say + - * or / only
and loop back to input box.

You have a lot of work to do.

I will help if you want. But I won't do it all for you.

**LadyB** · 03-14-2013, 12:39 AM

I have been working on this literally all day. I made strmathoperation a string because that's what my directions said, "declare a string variable called MathOperation to hold the math operation to perform." And I have to use the select case/case else structure.

Option Explicit
Dim intanswer As Integer
Dim intnum1 As Integer
Dim intnum2 As Integer
Dim strmathoperation As String

Public Sub Main()
intnum1 = Val(InputBox("Please enter the first operand", "First operand"))
intnum2 = Val(InputBox("Please enter the second operand", "Second operand"))
strmathoperation = Val(InputBox("Enter +,-,*,or / for math operation", "Math operation"))
    Call operation(intnum1, intnum2, strmathoperation)
    SendResult
End Sub

Private Function operation(intnum1 As Integer, intnum2 As Integer, strmathoperation As String)
Select Case strmathoperation
Case "+"
operation = intnum1 + intnum2
Case "-"
operation = intnum1 - intnum2
Case "*"
operation = intnum1 * intnum2
Case "/"
operation = intnum1 / intnum2
Case Else
operation = 0
End Select
End Function
Private Sub SendResult()
MsgBox "The answer is:  " & operation(intnum1, intnum2, strmathoperation)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call Main
End Sub

Again any help is much appreciated.