Option Explicit

Public Sub createKemet300()
    Const StartLine = 2
    
    Dim lineCnt As Long
    Dim cntSerie As Integer
    Dim cntStyleSize As Integer
    Dim cntVoltage As Integer
    Dim cntTolerance As Integer
    Dim cntCapValues As Integer
    Dim tmpI As Integer
    Dim tmpDbl As Double
    Dim tmpStr As String
    
    Dim Series As Collection
    Dim StyleSize As Collection
    Dim Voltages As Collection
    Dim Tolerances As Collection
    Dim CapacitorValues As Collection
    Dim startTime As Date
    Dim endTime As Date
    
    'Variablen und Konstanten für Werte zum Eintragen:
    Const SymbolPath = "Symbolpfadstring"
    Const SymbolRef = "Kondensator DIN/ANSI"
    Dim FootprintRef As String
    Const FootprintPath = "Footprintpfadstring"
    Dim Description As String
    Const Manufacturer = "Kemet"
    Dim ManufacturerNumber As String
    Const ComponentSeries = "GoldMax 300 Auto C0G Series"
    Const CmpLinkDescription = "Datenblatt"
    Const CmpLinkUrl = "Datenblattpfadstring"
    Dim PrimValue As String
    Dim PrimTolerance As String
    Const SecValue = "-"
    Const SecTolerance = "-"
    Dim OperatingVoltage As String
    Const OperatingTemperature = "-55°C - +125°C"
    Dim PackageInformation As String
    Const Standards = "UL 94V–0, RoHS, REACH"
    Const Grade = "Automotive"
    
    startTime = Now()
    
    'Parameter für die Berechnung der Tabellenwerte erstellen
    Set Series = New Collection
    With Series
        .Add "C31"
        .Add "C32"
        .Add "C33"
    End With
    Set StyleSize = New Collection
    With StyleSize
        .Add "315"
        .Add "316"
        .Add "317"
        .Add "318"
        .Add "320"
        .Add "321"
        .Add "322"
        .Add "323"
        .Add "324"
        .Add "325"
        .Add "326"
        .Add "327"
        .Add "328"
        .Add "330"
        .Add "331"
        .Add "333"
        .Add "335"
        .Add "336"
    End With
    Set Voltages = New Collection
    With Voltages
        .Add "25"
        .Add "50"
        .Add "100"
        .Add "200"
        .Add "250"
    End With
    Set Tolerances = New Collection
    With Tolerances
        .Add "0,1pF"
        .Add "0,25pF"
        .Add "0,5pF"
        .Add "1%"
        .Add "2%"
        .Add "5%"
        .Add "10%"
    End With
    Set CapacitorValues = createESeries(0.000000000001, 0.00000022, 24, eSerieskhochm)
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    'Schleifenparameter (von außen nach innen):
        'Serie - StyleSize - Spannung - Toleranz - Kapazität
    
    'Tabellenwerte berechnen
    cntSerie = 1
    cntVoltage = 1
    cntTolerance = 1
    cntCapValues = 1
    lineCnt = StartLine
    Do While cntSerie <= Series.Count
        cntStyleSize = 1
        Do While cntStyleSize <= StyleSize.Count
            cntVoltage = 1
            Do While cntVoltage <= Voltages.Count
                cntTolerance = 1
                Do While cntTolerance <= Tolerances.Count
                    cntCapValues = 1
                    Do While cntCapValues <= CapacitorValues.Count
                        'Hier die Werte berechnen, die nachher in der Tabelle landen sollen
                        
                        'Werte überspringen falls nötig weil es diese Kombinationen z.B. nicht gibt:
                        'Toleranzen-Kapazitätskombinationen überspringen die es nicht gibt
                        If CapacitorValues(cntCapValues) > 0.0000000000091 And cntTolerance <= 3 Then GoTo nextToleranceValue
                        If CapacitorValues(cntCapValues) <= 0.0000000000091 And cntTolerance > 3 Then GoTo nextCapacityValue
                        'Serien-Spannungs-Kapazitätskompinationen überspringen die es nicht gibt:
                        Select Case Series(cntSerie)
                            Case "C31", "C32":
                                Select Case Voltages(cntVoltage)
                                    Case "25": If CapacitorValues(cntCapValues) > 0.00000018 Then GoTo nextToleranceValue
                                    Case "50": If CapacitorValues(cntCapValues) > 0.00000015 Then GoTo nextToleranceValue
                                    Case "100": If CapacitorValues(cntCapValues) > 0.0000001 Then GoTo nextToleranceValue
                                    Case "200": If CapacitorValues(cntCapValues) > 0.000000047 Then GoTo nextToleranceValue
                                    Case "250": If CapacitorValues(cntCapValues) > 0.000000047 Then GoTo nextToleranceValue
                                End Select
                                
                            Case "C33":
                                Select Case Voltages(cntVoltage)
                                    Case "25":  If CapacitorValues(cntCapValues) > 0 Then GoTo nextToleranceValue
                                    Case "50":  If CapacitorValues(cntCapValues) > 0.00000022 Then GoTo nextToleranceValue
                                    Case "100":  If CapacitorValues(cntCapValues) > 0.00000015 Then GoTo nextToleranceValue
                                    Case "200":  If CapacitorValues(cntCapValues) > 0.0000001 Then GoTo nextToleranceValue
                                    Case "250":  If CapacitorValues(cntCapValues) > 0.0000001 Then GoTo nextToleranceValue
                                End Select
                        End Select
                        
                        'Serien-Gehäusekombinationen überspringen die es nicht gibt
                        Select Case Series(Series.Count)
                            Case "C31":
                                Select Case StyleSize(cntStyleSize)
                                    Case "315", "316", "317", "318":    'Tue nichts, gehe weiter
                                    Case Default: GoTo nextStyleSize    'Überspringe diesen Case, weiter mit nächstem Style/Size
                                End Select
                                
                            Case "C32":
                                Select Case StyleSize(cntStyleSize)
                                    Case "324", "320", "326", "321", "322", "323", "325", "328", "327":    'Tue nichts, gehe weiter
                                    Case Default: GoTo nextStyleSize    'Überspringe diesen Case, weiter mit nächstem Style/Size
                                End Select
                                
                            Case "C33":
                                Select Case StyleSize(cntStyleSize)
                                    Case "330", "331", "333", "335", "336":    'Tue nichts, gehe weiter
                                    Case Default: GoTo nextStyleSize    'Überspringe diesen Case, weiter mit nächstem Style/Size
                                End Select
                        End Select
                
                        'Werte berechnen
                        'Footprintreferenz:
                        FootprintRef = "C-" & StyleSize(cntStyleSize)
                        
                        'Beschreibung erstellen:
                        Description = "Low ESR/ESL, " & formatSI(CapacitorValues(cntCapValues), 1) & "F/" & ChrW(177) & Tolerances(cntTolerance)
                        
                        'Herstellerbezeichnung erstellen
                        ManufacturerNumber = "'"
                        ManufacturerNumber = "C" & StyleSize(cntStyleSize) & "C"
                        tmpStr = CapacitorValues(cntCapValues) * 10 ^ (-1 * Application.RoundUp(Log(CapacitorValues(cntCapValues)) / Log(10#), 0) + 1)
                        tmpStr = Format(Replace(tmpStr, ",", ""), "00")
                        tmpStr = tmpStr & (-1 * Application.RoundUp(Log(CapacitorValues(cntCapValues)) / Log(10#), 0) - 3) 'Anzahl der Nullen, wenn man die Kapazität im Format x,y pF schreibt
                        ManufacturerNumber = ManufacturerNumber & tmpStr
                        Select Case Tolerances(cntTolerance)
                            Case "0,1pF": ManufacturerNumber = ManufacturerNumber & "B"
                            Case "0,25pF": ManufacturerNumber = ManufacturerNumber & "C"
                            Case "0,5pF": ManufacturerNumber = ManufacturerNumber & "D"
                            Case "1%": ManufacturerNumber = ManufacturerNumber & "F"
                            Case "2%": ManufacturerNumber = ManufacturerNumber & "G"
                            Case "5%": ManufacturerNumber = ManufacturerNumber & "J"
                            Case "10%": ManufacturerNumber = ManufacturerNumber & "K"
                        End Select
                        Select Case Voltages(cntVoltage)
                            Case "25": ManufacturerNumber = ManufacturerNumber & "3"
                            Case "50": ManufacturerNumber = ManufacturerNumber & "5"
                            Case "100": ManufacturerNumber = ManufacturerNumber & "1"
                            Case "200": ManufacturerNumber = ManufacturerNumber & "2"
                            Case "250": ManufacturerNumber = ManufacturerNumber & "A"
                        End Select
                        ManufacturerNumber = ManufacturerNumber & "G5TA9170"
                        
                        'Primären Bauteilwert erstellen
                        PrimValue = "'" & formatSI(CapacitorValues(cntCapValues), 1) & "F"
                        
                        'Primäre Bauteiltoleranz, Betriebsspannung und Gehäusebeschreibung erstellen
                        PrimTolerance = "'" & ChrW(177) & Tolerances(cntTolerance)
                        OperatingVoltage = "'" & Voltages(cntVoltage) & "V"
                        PackageInformation = "(THT) C-" & StyleSize(cntStyleSize)
                        'Werte in die Tabelle eintragen
                        With ThisWorkbook.Worksheets("01 Keramik")
                            With .Range("A" & lineCnt)
                                .HorizontalAlignment = xlLeft
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = SymbolRef
                            End With
                            With .Range("B" & lineCnt)
                                .HorizontalAlignment = xlLeft
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .ShrinkToFit = True
                                .Value = SymbolPath
                            End With
                            With .Range("C" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = FootprintRef
                            End With
                            With .Range("D" & lineCnt)
                                .HorizontalAlignment = xlLeft
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .ShrinkToFit = True
                                .Value = FootprintPath
                            End With
                            With .Range("E" & lineCnt)
                                .HorizontalAlignment = xlLeft
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = Description
                            End With
                            With .Range("F" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = Manufacturer
                            End With
                            With .Range("G" & lineCnt)
                                .HorizontalAlignment = xlLeft
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = ManufacturerNumber
                            End With
                            With .Range("H" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = ComponentSeries
                            End With
                            With .Range("I" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .ShrinkToFit = True
                                .Value = CmpLinkDescription
                            End With
                            With .Range("J" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .ShrinkToFit = True
                                .Value = CmpLinkUrl
                            End With
                            With .Range("K" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = PrimValue
                            End With
                            With .Range("L" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = PrimTolerance
                            End With
                            With .Range("M" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = SecValue
                            End With
                            With .Range("N" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = SecTolerance
                            End With
                            With .Range("O" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = OperatingVoltage
                            End With
                            With .Range("P" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = OperatingTemperature
                            End With
                            With .Range("Q" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = PackageInformation
                            End With
                            With .Range("R" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = Standards
                            End With
                            With .Range("S" & lineCnt)
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                                .NumberFormat = "@"
                                .Value = Grade
                            End With
                            With .Range("A" & lineCnt & ":S" & lineCnt).Borders(xlInsideVertical)
                                .LineStyle = xlContinuous
                                .ColorIndex = xlAutomatic
                                .TintAndShade = 0
                                .Weight = xlThin
                            End With
                        End With
                        lineCnt = lineCnt + 1
nextCapacityValue:
                        cntCapValues = cntCapValues + 1
                    Loop
nextToleranceValue:
                    cntTolerance = cntTolerance + 1
                Loop
                cntVoltage = cntVoltage + 1
            Loop
nextStyleSize:
            cntStyleSize = cntStyleSize + 1
        Loop
        cntSerie = cntSerie + 1
    Loop
    
    endTime = Now()
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    MsgBox ("Fertig :)" & vbNewLine & vbNewLine & vbNewLine & "Ausführungszeit: " & vbNewLine & Format(endTime - startTime, "hh:mm:ss"))
End Sub