如何在VBA中使用编程创建的工作表函数

Is there a "Register" or "Re-compile" function needed before using programmatically created functions?

在使用编程创建的函数之前，是否需要一个“Register”或“Re-compile”函数?

When I add a function to a worksheet I cannot use it until after control is returned to the worksheet.

当我向工作表中添加一个函数时，在将控件返回到工作表之后，我才能使用它。

For example: If my code adds a function to a worksheet, then tries to use it I get the following error: Run-Time Error 438 - Object does not support this property or method When I look at the code for the worksheets the functions are there and if I run code that only uses the created functions, there is no error.

例如:如果我的工作表代码添加一个函数,然后试图使用它我得到以下错误:运行时错误438 -对象不支持此属性或方法当我看工作表函数的代码有如果我运行代码,只有使用创建的函数,没有错误。

How can I use the functions right after I create them, without stopping first?

如何在创建函数之后使用它们，而不首先停止?

Here is an example in code - I get the error when I run TestingWorkSheetFunctions but not when I run TestWorkSheetFunction after the functions are created.

下面是代码中的一个例子——我在运行testingworksheetfunction时出错，但是在创建函数之后运行TestWorkSheetFunction时出错。

Example assumes a new workbook with at least two sheets (sheet1 and sheet2)

示例假设有一个至少包含两个表的新工作簿(sheet1和sheet2)

Option Explicit

Public Sub TestingWorksheetFunction()

    AddWorkSheetFunction

    TestWorkSheetFunction

End Sub

Public Sub AddWorkSheetFunction()

    'Sheet1's Function
    Dim strFunctionCode As String
        strFunctionCode = _
            "Public Function HelloWorld() as string" & vbCrLf & _
            vbCrLf & _
            vbTab & "HelloWorld = ""Hello World from Sheet 1""" & vbCrLf & _
            vbCrLf & _
            "End Function"
    ThisWorkbook.VBProject.VBComponents(ThisWorkbook.Sheets("Sheet1").CodeName).CodeModule.AddFromString strFunctionCode

    'Sheet2's Function
    strFunctionCode = _
        "Public Function HelloWorld() as string" & vbCrLf & _
        vbCrLf & _
        vbTab & "HelloWorld = ""Hello World from Sheet 2""" & vbCrLf & _
        vbCrLf & _
        "End Function"
    ThisWorkbook.VBProject.VBComponents(ThisWorkbook.Sheets("Sheet2").CodeName).CodeModule.AddFromString strFunctionCode

End Sub

Public Sub TestWorkSheetFunction()

    Dim wsWorksheet1 As Object
    Set wsWorksheet1 = ThisWorkbook.Sheets("Sheet1")
    Dim wsWorksheet2 As Object
    Set wsWorksheet2 = ThisWorkbook.Sheets("Sheet2")

    MsgBox wsWorksheet1.HelloWorld()
    MsgBox wsWorksheet2.HelloWorld()

End Sub

2 个解决方案

#1

I think the problem here is that your vba is added to the worksheets and is not compiled, so that when the rest of your code tries to access this functions, they are written, but not part of the program yet. This can be seen when you run the code again and everything works fine.

我认为这里的问题是您的vba被添加到工作表中，并且没有编译，所以当您的其余代码试图访问这些函数时，它们是被编写的，但还不是程序的一部分。当您再次运行代码时，可以看到这一点，并且一切正常。

Please try to switch the code you have on the following procedure to:

请尝试将下列程序的代码转换为:

Public Sub TestingWorksheetFunction()

    AddWorkSheetFunction
    Application.OnTime Now, "TestWorkSheetFunction"

End Sub

This way, the vba will run the first part of the code and release the process, and then the procedure "TestWorkSheetFunction" will be called right away. Important: This is a workaround for your problem, this might not be the best solution, but it might work for your specific case.

这样，vba将运行代码的第一部分并释放进程，然后将立即调用过程“TestWorkSheetFunction”。重要提示:这是一个解决问题的方法，这可能不是最好的解决方案，但它可能适用于特定的情况。

#2

Excel is not able to call "sub" routines in cell formulas. You need to create them using a function procedure under a module to ensure that it is globally available to the entire document.

Excel不能调用单元格公式中的“子”例程。您需要使用模块下的函数过程创建它们，以确保整个文档都可以使用它。

Public Function testFunc()

    Code here

End Function

Now you can use the function within the sheet or other procedures.

现在您可以在表或其他过程中使用该函数。

Your function can call other Sub and Function type procedures in its code block.

您的函数可以在代码块中调用其他子和函数类型的过程。

#1