Hallo,
die Werte sind zu groß für Integer, du brauchst Long oder Double.
Ich habe mal auf die schnelle, ohne zu sehr auf alle regeln zu achten, deinen
Code etwas anders geschrieben.
Dort siehst du vielleicht, ein oder zwei Möglichkeiten, ewig lange
Verkettungen von IF Sätzen zu vermeiden(Schadet der Übersicht).
Persönlich schaue ich mir immer gerne fremden Code an, da man immer das ein
oder andere sich abgucken kann.
Beide Codes brauchen ungf. die gleiche Zeit (0,05 Sekunden).
Hier die Mappe mit Code(in Modul: Berechnung): https://www.herber.de/bbs/user/114849.xlsm
Hier nur Code:
Option Explicit
Private ws As Worksheet
Private rowValues()
Const PI As Double = 3.1417
Sub GetFormulaValues()
Dim cRow As Long, i As Long
Dim US_Umax As Double, US_Lmax As Double
Dim US_A As Double, US_D As Double
Dim define As String
Dim functionNumber As Byte
On Error Resume Next
ReDim rowValues(9)
Set ws = ThisWorkbook.Sheets("Aufstellung")
With ws
cRow = .Cells(.Rows.Count, 1).End(xlUp).Row
ClearRange cRow
For i = 13 To cRow
If .Cells(i, 4).Value "" Then
GetValue (i)
define = .Cells(i, 4).Value
functionNumber = ValidDefinedString(define)
.Cells(i, 29) = Round(GetFormulaData(functionNumber), 4)
End If
Next i
End With
End Sub
Private Sub ClearRange(ByVal cRow As Long)
Dim rng As Range
With ws
Set rng = .Range(.Cells(13, 29), .Cells(cRow, 29))
rng.ClearContents
End With
End Sub
Private Sub GetValue(ByVal currentRow As Long)
Dim i As Integer
With ws
For i = 6 To 15
rowValues(i - 6) = .Cells(currentRow, i)
Next i
End With
End Sub
Private Function ValidDefinedString(ByVal define As String) As Byte
Dim definingString As Variant
Dim i As Integer
definingString = Array("L", "LT", "SU", "BS", "BA", "WS", "WA", "US", "UA", _
"RS", "RA", "ES", "EA", "BO", "TG", "TA", "HS")
For i = 0 To 16
If define = definingString(i) Then ValidDefinedString = i + 1: Exit For
Next i
End Function
Private Function GetFormulaData(ByVal formulaNumber As Byte) As Double
Select Case formulaNumber
Case 1
GetFormulaData = L_Formula(rowValues(0), rowValues(1), rowValues(6))
Case 2
GetFormulaData = LT_Formula(rowValues(0), rowValues(1), rowValues(6))
Case 3
GetFormulaData = SU_Formula(rowValues(0), rowValues(1), rowValues(6), rowValues(3))
Case 4
GetFormulaData = BS_Formula(rowValues(0), rowValues(1), rowValues(6), rowValues(7), _
rowValues(4), rowValues(5))
Case 5
GetFormulaData = BA_Formula(rowValues(0), rowValues(1), rowValues(6), rowValues(7), _
rowValues(3), rowValues(4), rowValues(5), rowValues(2))
Case 6
GetFormulaData = WS_Formula(rowValues(0), rowValues(1), rowValues(4), rowValues(5))
Case 7
GetFormulaData = WA_Formula(rowValues(0), rowValues(1), rowValues(3), rowValues(4), _
rowValues(5), rowValues(2))
Case 8
GetFormulaData = US_Formula(rowValues(0), rowValues(1), rowValues(2), rowValues(3), _
rowValues(4), rowValues(5), rowValues(6))
Case 9
GetFormulaData = UA_Formula(rowValues(0), rowValues(1), rowValues(2), rowValues(3), _
rowValues(4), rowValues(5), rowValues(6))
Case 10
GetFormulaData = RS_Formula(rowValues(0), rowValues(1), rowValues(3), rowValues(5), _
rowValues(6), rowValues(4))
Case 11
GetFormulaData = RA_Formula(rowValues(0), rowValues(1), rowValues(3), rowValues(4), _
rowValues(5), rowValues(6))
Case 12
GetFormulaData = ES_Formula(rowValues(0), rowValues(1), rowValues(6), rowValues(4))
Case 13
GetFormulaData = EA_Formula(rowValues(0), rowValues(1), rowValues(3), rowValues(2), _
rowValues(4), rowValues(6))
Case 14
GetFormulaData = BO_Formula(rowValues(0), rowValues(1))
Case 15
GetFormulaData = TG_Formula(rowValues(0), rowValues(1), rowValues(2), rowValues(3), _
rowValues(6), rowValues(5))
Case 16
GetFormulaData = TA_Formula(rowValues(0), rowValues(1), rowValues(3), rowValues(2), _
rowValues(6), rowValues(5), rowValues(4))
Case 17
GetFormulaData = HS_Formula(rowValues(0), rowValues(1), rowValues(2), rowValues(3), _
rowValues(4), rowValues(5), rowValues(6))
End Select
End Function
Private Function L_Formula(ByVal a, b, g_L) As Double
L_Formula = 2 * (a + b) * g_L
End Function
Private Function LT_Formula(ByVal a, b, g_L) As Double
LT_Formula = 2 * g_L * (a + b) / 1000 / 1000
End Function
Private Function SU_Formula(ByVal a, b, g_L, d) As Double
SU_Formula = 2 * (a + b) * (g_L ^ 2 + (b - d) ^ 2) ^ 0.5 / 1000 / 1000
End Function
Private Function BS_Formula(ByVal a, b, g_L, r, e, f_h) As Double
BS_Formula = 2 * (a + b) * (PI * g_L * (r + b) / 180 + e + f_h) / 1000 / 1000
End Function
Private Function BA_Formula(ByVal a, b, g_L, r, d, e, f_h, c_m) As Double
If b >= d Then BA_Formula = 2 * (a + b) * (PI * g_L * (r + b) / 180 + e + f_h)
If b = d Then WA_Formula = 2 * (a + b) * (b + d + e + f_h)
If b = (c_m + d) Then US_Umax = 2 * (a + b) Else: US_Umax = 2 * (c_m + d)
If e >= f_h Then US_Lmax = (g_L ^ 2 + e ^ 2) ^ 0.5 Else: US_Lmax = (g_L ^ 2 + f_h ^ 2) ^ 0. _
5
US_Formula = US_Umax * US_Lmax
End Function
Private Function UA_Formula(ByVal a, b, c_m, d, e, f_h, g_L) As Double
Dim US_Umax As Double, US_Lmax As Double
If (a + b) >= (c_m + d) Then
US_Umax = 2 * (a + b)
If (b - d + e) >= e Then US_Lmax = (g_L ^ 2 + (b - d + e) ^ 2) ^ 0.5 Else: US_Lmax = ( _
g_L ^ 2 + e ^ 2) ^ 0.5
Else
US_Umax = 2 * (c_m + d)
If (a - c_m + f_h >= f_h) Then US_Lmax = (g_L ^ 2 + (a - c_m + f_h) ^ 2) ^ 0.5 Else: _
US_Lmax = (g_L ^ 2 + (f_h) ^ 2) ^ 0.5
End If
UA_Formula = US_Umax * US_Lmax
End Function
Private Function RS_Formula(ByVal a, b, d, f_h, g_L, e) As Double
Dim US_Umax As Double, US_Lmax As Double
If (a + b) >= (PI * d * 0.5) Then US_Umax = 2 * (a + b) Else: US_Umax = PI * d
If e >= f_h Then US_Lmax = (g_L ^ 2 + e ^ 2) ^ 0.5 Else: US_Lmax = (g_L ^ 2 + f_h ^ 2) ^ 0. _
5
RS_Formula = US_Umax * US_Lmax
End Function
Private Function RA_Formula(ByVal a, b, d, e, f_h, g_L) As Double
Dim US_Umax As Double, US_Lmax As Double
If (a + b) >= (PI * d * 0.5) Then
US_Umax = 2 * (a + b)
If (b - d + e >= e) Then US_Lmax = (g_L ^ 2 + (b - d + e) ^ 2) ^ 0.5 Else: US_Lmax = ( _
g_L ^ 2 + (e) ^ 2) ^ 0.5
Else
US_Umax = PI * d
If (a - d + f_h >= f_h) Then US_Lmax = (g_L ^ 2 + (a - d + f_h) ^ 2) ^ 0.5 Else: _
US_Lmax = (g_L ^ 2 + (f_h) ^ 2) ^ 0.5
End If
RA_Formula = US_Umax * US_Lmax
End Function
Private Function ES_Formula(ByVal a, b, g_L, e) As Double
ES_Formula = 2 * (a + b) * (g_L ^ 2 + e ^ 2) ^ 0.5
End Function
Private Function EA_Formula(ByVal a, b, d, c_m, e, g_L) As Double
Dim US_Umax As Double, US_Lmax As Double
If b >= d Then US_Umax = 2 * (a + b) Else: US_Umax = 2 * (c_m + d)
If (b - d + e) >= e Then US_Lmax = (g_L ^ 2 + (b - d + e) ^ 2) ^ 0.5 Else: US_Lmax = (g_L ^ _
2 + e ^ 2) ^ 0.5
EA_Formula = US_Umax * US_Lmax
End Function
Private Function BO_Formula(ByVal a, b) As Double
BO_Formula = a * b
End Function
Private Function TG_Formula(ByVal a, b, c_m, d, g_L, f_h) As Double
Dim US_Umax As Double, US_Lmax As Double
Dim US_D As Double, US_A As Double
If (a + b) >= (c_m + d) Then US_Umax = 2 * (a + b) Else: US_Umax = 2 * (c_m + d)
US_D = US_Umax * g_L
If (d + c_m - b) >= c_m Then US_Lmax = d + c_m - b Else: US_Lmax = c_m
US_A = US_Lmax * (2 * (g_L + f_h))
TG_Formula = US_D + US_A
End Function
Private Function TA_Formula(a, b, d, c_m, g_L, f_h, e) As Double
Dim US_Umax As Double, US_Lmax As Double
Dim US_D As Double, US_A As Double
If (b) >= (d) Then US_Umax = 2 * (a + b) Else: US_Umax = 2 * (c_m + d)
US_D = US_Umax * (g_L ^ 2 + e ^ 2) ^ 0.5
If (d + c_m - b - e) >= c_m Then US_Lmax = d + c_m - b - e Else: US_Lmax = c_m
US_A = US_Lmax * (2 * (g_L + f_h))
TA_Formula = US_D + US_A
End Function
Private Function HS_Formula(a, b, c_m, d, e, f_h, g_L) As Double
Dim US_Umax As Double, US_Lmax As Double
If b >= (d + c_m + f_h) Then US_Umax = 2 * (a + b) Else: US_Umax = 2 * (c_m + d + c_m + f_h) _
If (b - f_h - c_m - d + e) >= e Then US_Lmax = (g_L ^ 2 + (b - f_h - c_m - d + e) ^ 2) ^ 0. _
5 Else: US_Lmax = (g_L ^ 2 + e ^ 2) ^ 0.5
HS_Formula = US_Umax * US_Lmax
End Function