Decipher Macro

[Mojojojo]

Hi there, I am currently stuck with some calculation project. And I have gotten an excel sheet which can helps but I never decode a Macro before. Is there anyone that can help me decode it and let me know what is the formula for the calculation?



Sub Button2_Click()
Dim speedstr As String
Dim DesSpeed As Single


DesSpeed = Sheet2.Cells(47, 5)


speedstr = InputBox("Please enter the vessel speeds in m/s separated by commas")


If speedstr = "" Then MsgBox "Please enter at least one value": Exit Sub


Dim arr


arr = Split(speedstr, ",")


Dim i As Integer


Sheet12.range("B4:C34").ClearContents




Dim count As Integer


count = 0


If UBound(arr) > 20 Then MsgBox "Maximum 20 values allowed": Exit Sub


For i = 0 To UBound(arr)


'MsgBox "Speed No. " & i + 1 & " = " & arr(i)
Dim currspeed As Single
If arr(i) = "" Then Exit Sub

If i > 0 Then
If Val(arr(i)) < Val(arr(i - 1)) Then

MsgBox "Please input the values in ascending order"

Sheets("Speeds").Activate

Exit Sub
End If


End If


currspeed = arr(i)
If Val(arr(i)) = 0 Then MsgBox "Please input a non-zero value for speed": Exit Sub

Dim range As Excel.range
Dim resistance As Double
'range.Cells(2, i).Value = arr(i)





If i > 0 Then
If DesSpeed < Val(arr(i)) And DesSpeed > Val(arr(i - 1)) Then

Sheets("Inputs - Vessel").Cells(47, 5).Value = DesSpeed
Sheets("Graph").Cells(4 + count, 2).Value = DesSpeed
resistance = Sheets("Final Forces").Cells(24, 4).Value
Sheets("Graph").Cells(4 + count, 3).Value = Format(resistance, "#0.00")
Sheet12.range(Sheet12.Cells(4 + count, 2), Sheet12.Cells(4 + count, 4)).Interior.Color = vbBlue
count = count + 1
End If
End If

Sheets("Inputs - Vessel").Cells(47, 5).Value = arr(i)

resistance = Sheets("Final Forces").Cells(24, 4).Value

Sheets("Graph").Cells(4 + count, 2).Value = arr(i)
Sheets("Graph").Cells(4 + count, 3).Value = Format(resistance, "#0.00")


Sheets("Graph").Activate

count = count + 1

Next i




'''''''''Displaying the resistance at design speed
Sheets("Inputs - Vessel").Cells(47, 5).Value = DesSpeed


End Sub

[Paul_Hossler]

There's a number of sheets referenced in the macro that I have no idea

It's easier to trace a macro if it's indented and structured a little differently

See if you can trace this since you have a better idea of what it's supposed to do



Option Explicit


Sub Button2_Click()


Dim speedstr As String
Dim DesSpeed As Single
Dim arr As Variant
Dim i As Integer
Dim count As Integer
Dim currspeed As Single
Dim range As range
Dim resistance As Double


DesSpeed = Sheet2.Cells(47, 5)

speedstr = InputBox("Please enter the vessel speeds in m/s separated by commas")


If speedstr = "" Then
MsgBox "Please enter at least one value"
Exit Sub
End If


arr = Split(speedstr, ",")
Sheet12.range("B4:C34").ClearContents

count = 0


If UBound(arr) > 20 Then
MsgBox "Maximum 20 values allowed"
Exit Sub
End If


For i = 0 To UBound(arr)

'MsgBox "Speed No. " & i + 1 & " = " & arr(i)
If arr(i) = "" Then Exit Sub

If i > 0 Then
If Val(arr(i)) < Val(arr(i - 1)) Then
MsgBox "Please input the values in ascending order"
Sheets("Speeds").Activate
Exit Sub
End If
End If

currspeed = arr(i)

If Val(arr(i)) = 0 Then
MsgBox "Please input a non-zero value for speed"
Exit Sub
End If

'range.Cells(2, i).Value = arr(i)

If i > 0 Then

If DesSpeed < Val(arr(i)) And DesSpeed > Val(arr(i - 1)) Then
Sheets("Inputs - Vessel").Cells(47, 5).Value = DesSpeed
Sheets("Graph").Cells(4 + count, 2).Value = DesSpeed
resistance = Sheets("Final Forces").Cells(24, 4).Value
Sheets("Graph").Cells(4 + count, 3).Value = Format(resistance, "#0.00")
Sheet12.range(Sheet12.Cells(4 + count, 2), Sheet12.Cells(4 + count, 4)).Interior.Color = vbBlue
count = count + 1
End If
End If

Sheets("Inputs - Vessel").Cells(47, 5).Value = arr(i)

resistance = Sheets("Final Forces").Cells(24, 4).Value

Sheets("Graph").Cells(4 + count, 2).Value = arr(i)
Sheets("Graph").Cells(4 + count, 3).Value = Format(resistance, "#0.00")
Sheets("Graph").Activate

count = count + 1
Next i




'''''''''Displaying the resistance at design speed
Sheets("Inputs - Vessel").Cells(47, 5).Value = DesSpeed
End Sub