Creating a message box to take an X, Y, Z and perform Sum of Squares
Posted by Joe D on October 11, 2001 12:24 PM
Hello, I would greatly appreciate if anyone could help me out as I am new to Excel and VBA.
I want to create a macro that will generate a message box with the following:
A message saying "Please enter X, Y and Z to calculate Sum of Squares"
In the box there are three "input fields labeled X Y and Z.
After entering the numbers hit a button on the box to do the calculation.
ANSWER=SQR(X^2+Y^2+Z^2)
The box should then dissapear and the result should be pasted into Column J. The tricky part is that this could
be run multiple times and I don't want the new info to overwrite the previous info in Column J. It would actually
be best to insert a blank space and then write the new info.
I hope this isn't too confusing and I thank anyone who can help me for their time!!!
Regard,
Joe D
Posted by Jonathan on October 11, 2001 2:34 PM
You should look at the InputBox. This is what is used to gather input from a user. I do not think it takes more than ONE answer at a time (tho' I could be wrong). But you could always run it three times in succession as a start. Beyond that, if you want a set up as in your message, then I think you need to create a UserForm. You can make this into anything that you like, you're creating it pretty much from scratch.
Posted by Jerid on October 12, 2001 7:11 AM
Joe, this should do what you are looking for.
Sub SumOfSquares()
On Error GoTo ErrHandler
Dim dX As Double
Dim dY As Double
Dim dZ As Double
Dim dSumofSquares As Double
dX = CDbl(InputBox("Please enter X", "Calculate Sum of Squares"))
dY = CDbl(InputBox("Please enter Y", "Calculate Sum of Squares"))
dZ = CDbl(InputBox("Please enter Z", "Calculate Sum of Squares"))
dSumofSquares = Sqr((dX ^ 2) + (dY ^ 2) + (dZ ^ 2))
Application.Range("J1").Select
Do Until ActiveCell.Value = vbNullString
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = dSumofSquares
ExitHandler:
Exit Sub
ErrHandler:
MsgBox "An error has occured, this program will end!", vbCritical, "Verify data entered"
End Sub
