英文:
Excel VBA stack overflow issue
问题
这是我在这个论坛上的第一个问题，我不是Excel VBA专家（所以才会有这个问题😉）。在尝试根据Excel表中的其他值自动隐藏/显示行时，我遇到了堆栈溢出错误。
这些值实际上是以下形式的函数：
=IFERROR(IF(OR(C32="Yes",D32=1,E32=1,F32=1),1,0),0)
所以如果值为1，行应该显示，如果值为0，行应该隐藏。我使用了YouTube 视频来学习我需要的代码。总共有15个部分，我希望在Excel表中像这样隐藏它们。
然而，当我尝试对超过4个部分执行此操作时，有时Excel会崩溃，显示堆栈溢出错误。显然，有太多的过程堆叠在一起，因为其他Excel选项卡也包含正在运行的宏（基本上是相同的，根据值隐藏/显示；但在这些情况下，是从下拉菜单中选择的实际值，而不是函数；所以代码是不同的）。然而，错误总是涉及上面描述的部分。以下是我用于此的代码。有没有办法调试这个问题，或者拆分代码等，以避免错误？
非常感谢！
英文:
This is my first question in this forum and I am no Excel VBA expert whatsoever (hence the question ;). I am getting a stack overflow error when trying to automatically hide/unhide rows based on other values in the Excel sheet.
The values are actually functions in the form of:
=IFERROR(IF(OR(C32="Yes",D32=1,E32=1,F32=1),1,0),0)
So if the value is 1, the rows are supposed to unhide, if the value is 0, they are supposed to hide. I used a youtube video to learn what code I need for this. In total there are 15 sections that I want to hide in the Excel sheet like this.
However, I get the stack overflow error with the Excel sometimes crashing as soon as I try to do this for more than 4 sections. Apparently there are too many procedures stacking, because other Excel tabs also include macros that are running (basically the same, hiding/unhiding based on values; but in those cases actual values chosen from a drop-down and not functions; so the code is different). However, the error always concerns the part described above. Below is the code I used for it. Is there any way how to debug this, or split up the code, etc. in order to avoid the error?
Many thanks in advance!
Private Sub Worksheet_Calculate()
Dim OECD As Range
Set OECD = Range("G32")
Select Case OECD
Case Is = "1": Rows("33:38").EntireRow.Hidden = False
Case Is = "0": Rows("33:38").EntireRow.Hidden = True
End Select
Dim IndRights As Range
Set IndRights = Range("G43")
Select Case IndRights
Case Is = "1": Rows("44:46").EntireRow.Hidden = False
Case Is = "0": Rows("44:46").EntireRow.Hidden = True
End Select
Dim LandRights As Range
Set LandRights = Range("G47")
Select Case LandRights
Case Is = "1": Rows("48:50").EntireRow.Hidden = False
Case Is = "0": Rows("48:50").EntireRow.Hidden = True
End Select
Dim Wages As Range
Set Wages = Range("G51")
Select Case Wages
Case Is = "1": Rows("52:55").EntireRow.Hidden = False
Case Is = "0": Rows("52:55").EntireRow.Hidden = True
End Select
Dim Hrs As Range
Set Hrs = Range("G56")
Select Case Hrs
Case Is = "1": Rows("57:58").EntireRow.Hidden = False
Case Is = "0": Rows("57:58").EntireRow.Hidden = True
End Select
Dim Accomm As Range
Set Accomm = Range("G60")
Select Case Accomm
Case Is = "1": Rows("61:64").EntireRow.Hidden = False
Case Is = "0": Rows("61:64").EntireRow.Hidden = True
End Select
Dim Forest As Range
Set Forest = Range("G65")
Select Case Forest
Case Is = "1": Rows("66:67").EntireRow.Hidden = False
Case Is = "0": Rows("66:67").EntireRow.Hidden = True
End Select
Dim Water As Range
Set Water = Range("G68")
Select Case Water
Case Is = "1": Rows("69:71").EntireRow.Hidden = False
Case Is = "0": Rows("69:71").EntireRow.Hidden = True
End Select
Dim Biodiv As Range
Set Biodiv = Range("G72")
Select Case Biodiv
Case Is = "1": Rows("73:75").EntireRow.Hidden = False
Case Is = "0": Rows("73:75").EntireRow.Hidden = True
End Select
Dim Soil As Range
Set Soil = Range("G76")
Select Case Soil
Case Is = "1": Rows("77:80").EntireRow.Hidden = False
Case Is = "0": Rows("77:80").EntireRow.Hidden = True
End Select
Dim Waste As Range
Set Waste = Range("G81")
Select Case Waste
Case Is = "1": Rows("82:87").EntireRow.Hidden = False
Case Is = "0": Rows("82:87").EntireRow.Hidden = True
End Select
Dim Chem As Range
Set Chem = Range("G88")
Select Case Chem
Case Is = "1": Rows("89:92").EntireRow.Hidden = False
Case Is = "0": Rows("89:92").EntireRow.Hidden = True
End Select
Dim GhG As Range
Set GhG = Range("G93")
Select Case GhG
Case Is = "1": Rows("94:95").EntireRow.Hidden = False
Case Is = "0": Rows("94:95").EntireRow.Hidden = True
End Select
Dim Energy As Range
Set Energy = Range("G96")
Select Case Energy
Case Is = "1": Rows("97:98").EntireRow.Hidden = False
Case Is = "0": Rows("97:98").EntireRow.Hidden = True
End Select
End Sub
答案1
得分: 1
以下是翻译好的部分：
你可以用类似这样的代码替换你发布的整个过程：
对于每个 rg 在数组中（"33:38", "44:46", "48:50", "52:55", "57:58", "61:64", "66:67", "69:71", "73:75", "77:80", "82:87", "89:92", "94:95", "97:98"）
使用 rg 引用的行
.EntireRow.Hidden = (Range("G" & .Row - 1).Value = 0)
结束使用
结束循环
英文:
You could replace the entire procedure you've posted with something like this:
For Each rg In Array("33:38", "44:46", "48:50", "52:55", "57:58", "61:64", "66:67", _
"69:71", "73:75", "77:80", "82:87", "89:92", "94:95", "97:98")
With Rows(rg)
.EntireRow.Hidden = (Range("G" & .Row - 1).Value = 0)
End With
Next
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论