无法从公式中调用函数Excel Excel 2003 [英] Unable to hide row Excel 2003 from function invoked from formula

问题描述

我有这个非常简单的功能

 公共函数HRows（xx As String）
 BeginRow = 2 
 EndRow = 10 
'HideRows 
对于RowCnt = BeginRow To EndRow 
单元格（RowCnt，ChkCol）.EntireRow.Hidden = True 
下一个RowCnt 
结束函数
  
 
 
当从命令按钮调用时，它可以正常工作，当作为公式调用时，例如= HRows（A1） ，从工作表单元格，它不会在Excel 2003上做任何事情，它在Open Office Calc 4.1中工作。
 
 
 
这种情况发生在另一个空的电子表格 - 没有保护，没有评论，没有形状（在其他问题中被建议作为抑制剂）
 
 
 
最终，我想隐藏/显示电子表格的相关部分，具体取决于用户进入某些关键单元格 - 我不想添加命令按钮来控制隐藏。
解决方案
已经介绍了这种方法她e  https://stackoverflow.com/a/23232311/2165759 ，为了您的目的，代码如下：
 
 
 
 将代码放在VBAProject的一个模块中： 
 code>公共任务，PermitNewTasks，ReturnValue 
 
函数HideRowsUDF（lBegRow，lEndRow）'在工作表上使用此UDF 
如果IsEmpty（Tasks）然后TasksInit 
如果PermitNewTasks然后Tasks.Add Application.Caller，Array（lBegRow，lEndRow）
 HideRowsUDF = ReturnValue 
结束函数
 
函数HideRows（lFrom，lUpTo）实际上在此函数中执行的所有操作，它运行没有UDF限制
范围（行（lFrom），行（lUpTo））。EntireRow.Hidden = True 
 HideRows =Rows&来自 - & lUpTo& 隐藏
结束函数
 
 Sub TasksInit（）
设置Tasks = CreateObject（Scripting.Dictionary）
 ReturnValue =
 PermitNewTasks = True 
 End Sub 
  
 将代码放在Microsoft Excel对象的ThisWorkbook部分VBAProject： 
  Private Sub Workbook_SheetCalculate（ByVal Sh As Object）
 Dim Task，TempFormula 
如果IsEmpty（Tasks）Then TasksInit 
 Application.EnableEvents = False 
 PermitNewTasks = False 
任务中的每个任务
 TempFormula = Task.FormulaR1C1 
 ReturnValue = HideRows任务（任务）（0），任务（任务）（1））
 Task.FormulaR1C1 = TempFormula 
 Tasks.Remove任务
下一个
 Application.EnableEvents = True 
 ReturnValue =
 PermitNewTasks = True 
 End Sub 
  
 
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 
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.
解决方案
I've already introduced this method here https://stackoverflow.com/a/23232311/2165759, for your purpose a code will be as follows:

Place code to one of the module of 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
Place code to ThisWorkbook section of Microsoft Excel Objects in VBAProject:
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 Excel 2003的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！