excel VBA中的堆栈溢出错误疑难解答

gdx19jrr 于 2023-06-30 发布在其他

关注(0)|答案(1)|浏览(224)

我试图编写一个程序，在VBA中执行计算，其中它引用程序中多行的单元格值，并将该值放置在表中的另一行中。目前，它正在查找正确的单元格值;然而，当它是时间来执行计算，它是给我一个堆栈溢出错误。我一直在试图解决这个问题，但我不知道还能做什么，因为我已经正确定义了所有变量。

Sub Calculate()
    Dim row As Integer 
    Dim frm As Worksheet
    Dim database As Worksheet
     
    Set frm = ThisWorkbook.Sheets("Installation Grid Test Form")    
    Set database = ThisWorkbook.Sheets("Installation Grid Test Database")   

'Calcualtion 10 PSI 1st Step K Value'
 
    With database                 
         For row = Int(2) To Int(1000)           
                If Cells(row, 3).Value = 610 Then
                      .Cells(row, 11).Value = ((Cells(row, 10).Value / 3600) / 386) / (0.525 * ((0.5 / 25.4) ^ 2) * ((60.81) ^ 0.5) * (Cells(row, 9).Value) ^ 0.5)  
                   ElseIf Cells(row, 3).Value = 620 Then                   
                        .Cells(row, 11).Value = ((Cells(row, 10).Value / 3600) / 84) / (0.525 * ((0.5 / 25.4) ^ 2) * ((60.81) ^ 0.5) * (Cells(row, 9).Value) ^ 0.5)
                   ElseIf Cells(row, 3).Value = 630 Then                   
                        .Cells(row, 11).Value = ((Cells(row, 10).Value / 3600) / 70) / (0.525 * ((0.5 / 25.4) ^ 2) * ((60.81) ^ 0.5) * (Cells(row, 9).Value) ^ 0.5)
                   ElseIf Cells(row, 3).Value = 910 Then                    
                       .Cells(row, 11).Value = ((Cells(row, 10).Value / 3600) / 312) / (0.525 * ((0.5 / 25.4) ^ 2) * ((60.81) ^ 0.5) * (Cells(row, 9).Value) ^ 0.5)
                   ElseIf Cells(row, 3).Value = 920 Then
                        .Cells(row, 11).Value = ((Cells(row, 10).Value / 3600) / 84) / (0.525 * ((0.5 / 25.4) ^ 2) * ((60.81) ^ 0.5) * (Cells(row, 9).Value) ^ 0.5)
                   ElseIf Cells(row, 3) = 930 Then                   
                       .Cells(row, 11).Value = ((Cells(row, 10).Value / 3600) / 70) / (0.525 * ((0.5 / 25.4) ^ 2) * ((60.81) ^ 0.5) * (Cells(row, 9).Value) ^ 0.5)
               End If
            Next row
    End With
           
End Sub

excel

来源：https://stackoverflow.com/questions/76568743/troubleshooting-stack-overflow-error-in-vba

1条答案

按热度按时间

eeq64g8w1#

检查第I列中的值不为空，否则除以零将产生溢出错误。

Option Explicit

Sub Calculate()
    
    Dim frm As Worksheet, database As Worksheet  
    Set frm = ThisWorkbook.Sheets("Installation Grid Test Form")
    Set database = ThisWorkbook.Sheets("Installation Grid Test Database")

    'Calculation 10 PSI 1st Step K Value'
    Dim r As Long, colC As Long
    Dim c As Double, i As Double, j As Double
    
    Const X as Double = 0.525 * ((0.5 / 25.4) ^ 2) * (60.81 ^ 0.5)
    With database
        For r = 2 To 1000
            colC = .Cells(r, "C")
            Select Case colC
                Case 610: c = 386
                Case 620, 920: c = 84
                Case 630, 930: c = 70
                Case 910: c = 312
                Case Else
                c = 0
            End Select
                ' calc
            If c <> 0 Then
                i = .Cells(r, "I").Value ^ 0.5
                If i <> 0 Then
                    j = .Cells(r, "J").Value / 3600
                    'Debug.Print r, colC, c, i, j, X
                    .Cells(r, "K").Value = j / (c * X * i)
                End If
            End If
        Next
    End With
           
End Sub

赞(0）回复(0）举报 2023-06-30

我来回答

excel VBA中的堆栈溢出错误疑难解答

1条答案

相关问题

热门标签

最新问答