La variabile non cambia mentre attraversa il ciclo

Sono un programmatore di prima volta che prova ad imparare Excel VBA, correndo in qualche problema.

Il programma è in esecuzione per calcolare la dimensione appropriata di una serranda per l'area disponibile. Tutto è calcolato correttamente, tranne per la variabile DampW mentre passa attraverso il ciclo. Poiché calcola le diverse size degli ammortizzatori, la variabile DampW non cambia quando viene modificata la variabile DampH. Il DampH è regolato nel FOR Loop.

Ho anche notato che quando si esegue contro le condizioni attuali il programma dovrebbe uscire, in quanto la condizione di DampCheck è uguale a "OK". Tuttavia, il programma entra nel loop.

Fathemes sapere se c'è qualche confusione su ciò che chiedo. Vuoi assicurarmi di averlo inviato correttamente.

Public Sub DamperCalcs1() DampType = Range("DampType").Value 'Activates the damper spreadsheet Sheets(DampType).Activate 'Calculates the maximum height allowed from the spreadsheet with damper sizes 'Counts the number of 0 in column "O" and returns the maximum height MaxH1 = Application.WorksheetFunction.Max(ActiveSheet.Range("$C$7:$C$" & 2 + Application.WorksheetFunction.CountIf(ActiveSheet.Range("$O:$O"), 0))) 'Pulls out the minimum height of the specified damper for the air handling unit from the damper spreadsheet MinH = Application.WorksheetFunction.Min(ActiveSheet.Range("$C$7:$C$7000")) 'Calculates the maximum Height allowed for damper sizing calculations If DampType = "None" Then MaxH2 = Sheets("inputs Performance").Range("$C$12") ElseIf Sheets("Inputs Performance").Range("$C$12") > MaxH1 Then MaxH2 = MaxH1 Else MaxH2 = Sheets("Inputs Performance").Range("$C$12") End If 'Calculates a true damper height with standard blade configuration 'Setting max height value as an integer with a calculation MaxH3 = [(MaxH2-3.75)/5.75] 'Calculates the Maximum damper height for calculations If (MaxH3 * 5.75) < MinH Then MaxH4 = MinH Else MaxH4 = (MaxH3 * 5.75) + 3.75 End If 'Calculates the height addition for a louver If Left(DampType, 3) = "ELF" Then DampH = 3 + Application.WorksheetFunction.Max(MaxH4, 9.5) Else DampH = Application.WorksheetFunction.Max(MaxH4, 9.5) End If 'Outputs the maximum and minimum values required Sheet33.Range("B2").Value = MaxH1 Sheet33.Range("B3").Value = MinH Sheet33.Range("B5").Value = MaxH2 'Outputs the actual damper height Sheet33.Range("B7").Value = DampH End Sub 

Inizio del secondo programma secondario

 Public Sub DamperCalcs2() CFM = Range("CFM").Value MaxFPM = Range("MaxFPM").Value 'Assigns a given value for variables required to calculate the EAML damper EAMLA = 3.23750323750089E-07 EAMLB = -9.29262202444403E-03 EAMLC = 3.82761707988981E-03 EAMLD = -3.44782545737092E-02 EAMLE = 5.00341409432224E-07 EAMLF = 8.40487603305808E-03 If Left(DampType, 3) = "ELF" Then DampW = [RoundUp((((CFM / MaxFPM - Application.WorksheetFunction.VLookup(DampH, Sheets("Free Area").Range("$B$113:$E$132"), 2, False)) / Application.WorksheetFunction.VLookup(DampH, Sheets("Free Area").Range("$B$113:$E$132"), 4, False)) + 12), 0)] ElseIf DampType = "EAML" Then DampW = [RoundUp((-(EAMLD + EAMLC * DampH) + SQRT((EAMLD + EAMLC * DampH) ^ 2 - (4 * (EAMLE * (-(CFM / MaxFPM) + EAMLF + EAMLA * DampH * DampH + EAMLB * DampH))))) / (2 * EAMLE), 0)] Else DampW = [RoundUp((144 * (CFM / MaxFPM) / DampH), 0)] End If DampType = Range("DampType").Value 'Selects the damper spreadsheet Sheets(DampType).Select DampRange = ("$C$3:$C$" & 1 + Application.WorksheetFunction.CountIf(ActiveSheet.Range("$O:$O"), 0)) 'number of heights in total range less than input height (use as origin for adjusted range on widths) DampCount1 = Application.WorksheetFunction.CountIf(ActiveSheet.Range(DampRange), "<" & DampH) + 3 'number of heights in total range equal input height (use as origin for adjusted range on widths) DampCount2 = Application.WorksheetFunction.CountIf(ActiveSheet.Range(DampRange), "=" & DampH) 'Sheets("Calc Performance 2").Activate DampType = Range("DampType").Value 'Selects the damper spreadsheet Sheets(DampType).Activate 'Creates a new range for damper heights AdjDampRange = ("D" & DampCount1 & ":D" & (DampCount1 + DampCount2 - 1)) 'number of widths in adjusted range less than clculated width DampWCount = Application.WorksheetFunction.CountIf(ActiveSheet.Range(AdjDampRange), "<" & DampW) 'Calculates the line number to pull the actual damper width from ActualDampWLine = DampCount1 + DampWCount 'Pulls the actual damper width from the spreadsheet ActualDampW = Range("D" & ActualDampWLine).Value DampLoc = Range("DampLoc").Value 'Calculates the hood depth for the max height of damper If Left(DampType, 3) = "ELF" Or DampType = "EAML" Or DampLoc = "TOP" Or DampLoc = "BOTTOM" Then Hood1 = 0 ElseIf Left(DampType, 3) = "AMS" And DampLoc = "Front" Then Hood1 = [RoundUp((DampH*DampW*1200)/((DampW+4)*1000)+8,0)] ElseIf Left(DampType, 3) = "AMS" And DampLoc = "Side" Then Hood1 = [RoundUp((DampH*DampW*1200)/((DampW+4)*1000)+16,0)] ElseIf Left(DampType, 2) = "CD" And DampLoc = "Front" Then Hood1 = [RoundUp((DampH*DampW*1200)/((DampW+4)*1000),0)] ElseIf Left(DampType, 2) = "CD" And DampLoc = "Side" Then Hood1 = [RoundUp((DampH*DampW*1200)/((DampW+4)*1000)+8,0)] End If 'Determines if Damper or Louver is acceptable If Sheets(DampType).Cells(3, ActualDampWLine) > DampH Or DampH > MaxH Or ActualDampW > MaxW Then 'Removed the following from check to tryout program Or Hood1 > 60 DampCheck = "X" Else DampCheck = "OK" End If 'Outputs the actual damper height Sheet33.Range("B9").Value = DampW 'Outputs the actual damper height Sheet33.Range("B11").Value = DampRange Sheet33.Range("B12").Value = DampCount1 Sheet33.Range("B13").Value = DampCount2 Sheet33.Range("B15").Value = AdjDampRange Sheet33.Range("B16").Value = DampWCount Sheet33.Range("B18").Value = ActualDampWLine Sheet33.Range("B19").Value = ActualDampW Sheet33.Range("B20").Value = Hood1 Sheet33.Range("B22").Value = DampCheck Sheets("Calc Performance 2").Select End Sub 

Programma utilizzato per call i due sottoprogrammi con loop inclusi.

 Public Sub TrialCalc3() Call DamperCalcs1 Call DamperCalcs2 If DampCheck = "OK" Then Sheet33.Range("B24").Value = "Macro Finished on first run" Else For LoopCount = 1 To 6 If DampCheck = "OK" Then Sheet33.Range("B24").Value = "Macro Finished between Loop runs" Exit Sub Else DampH = DampH - 5.75 Call DamperCalcs2 End If Next End If 'Outputs the actual damper height Sheet33.Range("C7").Value = DampH Sheet33.Range("C9").Value = DampW 'Outputs the actual damper height Sheet33.Range("C11").Value = DampRange Sheet33.Range("C12").Value = DampCount1 Sheet33.Range("C13").Value = DampCount2 Sheet33.Range("C15").Value = AdjDampRange Sheet33.Range("C16").Value = DampWCount Sheet33.Range("C18").Value = ActualDampWLine Sheet33.Range("C19").Value = ActualDampW Sheet33.Range("C20").Value = Hood1 Sheet33.Range("C22").Value = DampCheck End Sub Uscire da Sub Public Sub TrialCalc3() Call DamperCalcs1 Call DamperCalcs2 If DampCheck = "OK" Then Sheet33.Range("B24").Value = "Macro Finished on first run" Else For LoopCount = 1 To 6 If DampCheck = "OK" Then Sheet33.Range("B24").Value = "Macro Finished between Loop runs" Exit Sub Else DampH = DampH - 5.75 Call DamperCalcs2 End If Next End If 'Outputs the actual damper height Sheet33.Range("C7").Value = DampH Sheet33.Range("C9").Value = DampW 'Outputs the actual damper height Sheet33.Range("C11").Value = DampRange Sheet33.Range("C12").Value = DampCount1 Sheet33.Range("C13").Value = DampCount2 Sheet33.Range("C15").Value = AdjDampRange Sheet33.Range("C16").Value = DampWCount Sheet33.Range("C18").Value = ActualDampWLine Sheet33.Range("C19").Value = ActualDampW Sheet33.Range("C20").Value = Hood1 Sheet33.Range("C22").Value = DampCheck End Sub 

Variabili globali come assegnato alla parte superiore del module

 Option Explicit Public Damptype As String, DampTab As Variant, MaxH1 As Double, MaxH2 As Double, MinH As Double, MaxH3 As Integer, MaxH4 As Double, DampH As Double, MaxH As Double, MaxW As Double Public CFM As Double, MaxFPM As Double, DampW As Double, EAMLA As Double, EAMLB As Double, EAMLC As Double, EAMLD As Double, EAMLE As Double, EAMLF As Double Public DampRange As Variant, DampCount1 As Double, DampCount2 As Double Public AdjDampRange As Variant, DampWCount As Double, ActualDampW As Double, ActualDampWLine As Double, DampCheck As String Public DampLoc As String, Hood1 As Integer, LoopCount As Integer Opzione esplicita Option Explicit Public Damptype As String, DampTab As Variant, MaxH1 As Double, MaxH2 As Double, MinH As Double, MaxH3 As Integer, MaxH4 As Double, DampH As Double, MaxH As Double, MaxW As Double Public CFM As Double, MaxFPM As Double, DampW As Double, EAMLA As Double, EAMLB As Double, EAMLC As Double, EAMLD As Double, EAMLE As Double, EAMLF As Double Public DampRange As Variant, DampCount1 As Double, DampCount2 As Double Public AdjDampRange As Variant, DampWCount As Double, ActualDampW As Double, ActualDampWLine As Double, DampCheck As String Public DampLoc As String, Hood1 As Integer, LoopCount As Integer 

Aggiunto le seguenti linee di codice nel calcolo di DampW per vedere cosa stavo ottenendo.

 If Left(Damptype, 3) = "ELF" Then DampW = [RoundUp((((CFM / MaxFPM - Application.WorksheetFunction.VLookup(DampH, Sheets("Free Area").Range("$B$113:$E$132"), 2, False)) / Application.WorksheetFunction.VLookup(DampH, Sheets("Free Area").Range("$B$113:$E$132"), 4, False)) + 12), 0)] ElseIf Damptype = "EAML" Then DampW = [RoundUp(144 * (CFM / MaxFPM) / DampH, 0)] Else DampW = [RoundUp(144 * (CFM / MaxFPM) / DampH, 0)] MsgBox "MaxFPM is " & MaxFPM & " and Damper Height is " & DampH & " and New Damper Width " & DampW End If 

Quello che ho come output è elencato di seguito:

MaxFPM è pari a 1800 (Standard per tutti i calcoli DampW) DampH è uguale a 61,25 (Quale è l'altezza corretta quando viene regolato) DampW è uguale a 36 (Quale è il numero originale, dovrebbe essere 40 dopo il calcolo)

La width degli ammortizzatori a questo punto dovrebbe essere regolata a 33, invece che rimane all'originale 36. Non capisco cosa ho fatto male. Qualsiasi aiuto è apprezzato.

Il codice sembra ragionevole, ma i Subs non stanno comunicando correttamente. Ad esempio, Sub TrialCalcs3 esamina la variabile DampCheck . Ma non può "vedere" la variabile, perché non è globale nel field di applicazione.

Perché le routine siano in grado di "condividere" le variables, devono essere globali in ambito (che è pubblico) o passare come parametri.