无法从公式调用的函数中隐藏行 Excel 2003 [英] Unable to hide row Excel 2003 from function invoked from formula
问题描述
我有这个非常简单的功能
I have this very simple function
Public Function HRows(xx As String)
BeginRow = 2
EndRow = 10
' HideRows
For RowCnt = BeginRow To EndRow
Cells(RowCnt,ChkCol).EntireRow.Hidden = True
Next RowCnt
End Function
当从命令按钮调用时它工作正常,当作为公式调用时,例如 =HRows(A1),从工作表单元格调用它在 Excel 2003 上没有任何作用,它在 Open Office Calc 4.1 中工作
When invoked from a command button it works fine, when invoked as a formula, e.g =HRows(A1), from a worksheet cell it doesn't do anything on Excel 2003, it does work in Open Office Calc 4.1
这发生在一个空的电子表格上 - 没有保护,没有评论,没有形状(在其他问题中被建议作为抑制剂)
This happens on an otherwise empty spreadsheet - no protection, no comments, no shapes (which have been suggested as inhibitors in other questions)
最终,我想隐藏/显示电子表格的相关部分,这取决于用户在某些关键单元格中输入的内容 - 我不想添加命令按钮来控制隐藏.
Eventually, I want to hide/show the relevant sections of a spreadsheet, depending on what the user enters in certain key cells - I don't want to have to add command buttons to control the hiding.
推荐答案
我已经在这里介绍了这个方法 https://stackoverflow.com/a/23232311/2165759,为了您的目的,代码如下:
I've already introduced this method here https://stackoverflow.com/a/23232311/2165759, for your purpose a code will be as follows:
将代码放入 VBAProject 的模块之一:
Public Tasks, PermitNewTasks, ReturnValue
Function HideRowsUDF(lBegRow, lEndRow) ' Use this UDF on the sheet
If IsEmpty(Tasks) Then TasksInit
If PermitNewTasks Then Tasks.Add Application.Caller, Array(lBegRow, lEndRow)
HideRowsUDF = ReturnValue
End Function
Function HideRows(lFrom, lUpTo) ' actually all actions performed within this function, it runs without UDF limitations
Range(Rows(lFrom), Rows(lUpTo)).EntireRow.Hidden = True
HideRows = "Rows " & lFrom & "-" & lUpTo & " were hidden"
End Function
Sub TasksInit()
Set Tasks = CreateObject("Scripting.Dictionary")
ReturnValue = ""
PermitNewTasks = True
End Sub
将代码放置到 VBAProject 中 Microsoft Excel 对象的 ThisWorkbook 部分:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim Task, TempFormula
If IsEmpty(Tasks) Then TasksInit
Application.EnableEvents = False
PermitNewTasks = False
For Each Task In Tasks
TempFormula = Task.FormulaR1C1
ReturnValue = HideRows(Tasks(Task)(0), Tasks(Task)(1))
Task.FormulaR1C1 = TempFormula
Tasks.Remove Task
Next
Application.EnableEvents = True
ReturnValue = ""
PermitNewTasks = True
End Sub
这篇关于无法从公式调用的函数中隐藏行 Excel 2003的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!