VBA Create a Parabola

Excel allows creating programs using VBA (Visual Basic Applications).

This program shows how to create a parabola witb VBA:

Private Sub CmdBotton_Click()
Dim dblB As Double
Dim dblH As Double

‘User introduce values
dblB = InputBox(“please introduce a value for Base”, “Base Value”)
dblH = InputBox(“please introduce a value for Height”, “Height Value”)

‘Output value to Excel sheet
Range(“D17”).Value = dblH
Range(“D16”).Value = dblB

‘Use formula
Dim dblL As Double
Dim dblLOne As Double
Dim dblLTwo As Double
Dim dblLThree As Double

dblLOne = Sqr((4 * (dblH ^ 2)) + (dblB ^ 2))
dblLTwo = ((dblB ^ 2) / (2 * dblH))
dblLThree = Log(((2 * dblH) + dblLOne) / dblB)

dblL = dblLOne + (dblLTwo * dblLThree)

‘Display result in a message box
MsgBox (“The lenght of a segment with a base ” & dblB & ” and a Height ” & dblH & ” is ” & dblL)

‘Display result in excel Cell
Range(“D18”).Value = dblL

Dim intCounter As Integer
intCounter = 0

Dim intCountH As Integer
intCountH = 0

Dim intCountB As Integer
intCountB = 0
‘==defined to collect the errors of the three attemps, if this is required==
Dim strTryOne As String
Dim strTryTwo As String
Dim strTryThree As String

‘===testing for 3 loops ===

Do While intCounter < 3 ‘===H and B testing==
If intCounter < 3 Then
If dblB <= 0 Or dblH <= 0 Then ‘If any value is smaller or equal than 0
If dblB <= 0 And intCountB < 3 Then ‘If B is smaller or equal than 0 and it haven’t been tested more than3

If intCounter = 1 Then
strTryOne = “on your first try Base value was bigger than 0”
ElseIf intCounter = 2 Then
strTryTwo = “on your second try Base value was bigger than 0”
Else
strTryThree = “on your third try Base value was bigger than 0”
End If

dblB = InputBox(“Please introduce a value bigger than 0 for Base”)
intCountB = intCountB + 1

ElseIf dblH <= 0 And intCountH < 3 Then If intCounter = 1 Then strTryOne = “on your first try Height value was bigger than 0” ElseIf intCounter = 2 Then strTryTwo = “on your second try Height value was bigger than 0” Else strTryThree = “on your third try Height value was bigger than 0” End If dblH = InputBox(“Please introduce a value bigger than 0 for Height”) intCountH = intCountH + 1 Else If intCounter = 1 Then strTryOne = “on your first try Height and Base values were bigger than 0” ElseIf intCounter = 2 Then strTryTwo = “on your second try Height and Base values were bigger than 0” Else strTryThree = “on your third try Height and Base values were bigger than 0” End If dblB = InputBox(“Please introduce a value bigger than 0 for Base”) dblH = InputBox(“Please introduce a value bigger than 0 for Height”) intCountB = intCountB + 1 intCountH = intCountH + 1 End If End If Else ‘=== Question 1f=== MsgBox (strTryOne & “. ” & strTryTwo & “. ” & strTryThree & “.”) End If intCounter = intCounter + 1 Loop ‘Display final values Range(“D27”).Value = dblH Range(“D26”).Value = dblB If dblH >= 0 And dblH >= 0 Then
Range(“D28”).Value = dblL
Else
Range(“D28”).Value = (“The lenght haven’t been calculated. All Base and Heigh needed to be positive. And you put: ” & strTryOne & “. ” & strTryTwo & “. ” & strTryThree & “.”)
End If

End Sub

Advertisements