vba 子或函数未定义

Sub or Function not defined

提问by Matt Ridge

Being self taught I have not learned everything there is to know about Excel VBA. Hell lets be honest, I know some basics, but not as much as I probably should, so this question although probably a generic basic question, probably should be known by someone who knows VBA, but in the case of I learn what I need to move forward... so I need an explanation.

通过自学，我还没有学到有关 Excel VBA 的所有知识。老实说，我知道一些基础知识，但没有我应该知道的那么多，所以这个问题虽然可能是一个通用的基本问题，可能应该被知道 VBA 的人知道，但在我学习我需要的情况下继续前进……所以我需要一个解释。

When do you need to define a Sub or Function? Why do you need to define a Sub or Function? What is the purpose of defining a Sub or Function?

什么时候需要定义 Sub 或 Function？为什么需要定义 Sub 或 Function？定义 Sub 或 Function 的目的是什么？

I only ask, because of this:

我只问，因为这个：

Option Explicit

Sub OpenOrderReportExport()

    Dim wsJL As Worksheet   'Jobs List
    Dim wsPOT As Worksheet  'PO Tracking
    Dim wsTNO As Worksheet  'Tel-Nexx OOR
    Dim wbBK2 As Workbook   'New Workbook
    Dim wsWS1 As Worksheet  'Sheet1
    Dim wsWS2 As Worksheet  'Sheet2
    Dim wsWS3 As Worksheet  'Sheet3


    Set wsJL = Sheets("Jobs List")      'Jobs List
    Set wsPOT = Sheets("PO Tracking")   'PO Tracking
    Set wsTNO = Sheets("Tel-Nexx OOR")  'Tel-Nexx OOR
    Set wbBK2 = Workbook("Book2")       'New Workbook
    Set wsWS1 = Sheets("Sheet1")        'Sheet1
    Set wsWS2 = Sheets("Sheet2")        'Sheet2
    Set wsWS3 = Sheets("Sheet3")        'Sheet3

        Application.ScreenUpdating = False    ' Prevents screen refreshing.
        CurrentFile = ThisWorkbook.FullName
        NewFileType = "Excel Files 2007 (*.xlsx)"
        NewFile = Application.GetSaveAsFilename(InitialFileName:="Open Order Log - " & Format(Date, "dd-mm-yyyy") & ".txt", fileFilter:=NewFileType)

End Sub

Why does this sub need to be defined? I am putting it as a standard module, and working on setting it up so that once a button is pushed I will be able to move a few pages over to a new workbook, delete the blank ones, and go from there with a save function that you tell it where to save.

为什么需要定义这个sub？我把它作为一个标准模块，并致力于设置它，这样一旦按下按钮，我就可以将几页移到一个新的工作簿，删除空白的，然后使用保存功能从那里开始你告诉它在哪里保存。

I have a long way to go, but I wanted to test out the save function, and go from there, because to me that is going to be the hardest thing.

我还有很长的路要走，但我想测试保存功能，然后从那里开始，因为对我来说这将是最难的事情。

So without solving my entire conundrum, I would like to know why something like this needs to be defined? And how it needs to be defined... if someone could give an answer it be greatly appreciated.

所以没有解决我的整个难题，我想知道为什么需要定义这样的东西？以及它需要如何定义......如果有人能给出答案，将不胜感激。

回答by Siddharth Rout

Your post revolves around the following questions so let me explain them one by one.

您的帖子围绕以下问题展开，让我一一解释。

YOUR QUESTIONS

你的问题

1. When do you need to define a Sub or Function?
2. Why do you need to define a Sub or Function?
3. What is the purpose of defining a Sub or Function?
4. Why does this sub need to be defined?

1. 什么时候需要定义 Sub 或 Function？
2. 为什么需要定义 Sub 或 Function？
3. 定义 Sub 或 Function 的目的是什么？
4. 为什么需要定义这个sub？

VBA Supports two types of procedures: Sub Proceduresand Function procedures.

VBA 支持两种类型的过程：子过程和函数过程。

A Sub procedureconsists of series of statements and can be executed in a number of ways for example

甲Sub过程由一系列的语句的，并且可以在许多例如方式执行

Sub Sample()
    MsgBox "Hello Matt Ridge"
End Sub

or

或者

Sub Sample()
    Sample1 "Hello Matt Ridge"
End Sub

Sub Sample1(s As String)
    MsgBox s
End Sub

A VBA Functionon the other hand returns a single value or possibly an array. A function can be called from another VBA procedure or used in a worksheet formula. For example

另一方面，VBA函数返回单个值或可能是一个数组。可以从另一个 VBA 过程调用函数或在工作表公式中使用函数。例如

Sub Sample()
    Dim Ret As String

    Ret = GreetUser("Matt Ridge")

    MsgBox Ret
End Sub

Public Function GreetUser(User As String)
    GreetUser = "Hello " & User
End Function

The same function can also be called in a worksheet. For example of Cell A1 has Matt Ridgeand when you type this formula in cell B1

也可以在工作表中调用相同的函数。例如，单元格 A1 具有Matt Ridge，当您在单元格 B1 中键入此公式时

=GreetUser(A1)

You will get Hello Matt Ridgein Cell B1. See the below snapshot.

您将进入Hello Matt Ridge单元格 B1。请参阅下面的快照。

Now looking at your code.

现在看看你的代码。

When you say

当你说

    Dim wbBK2 As Workbook 

Then VBA expects you to set the variable accordingly as enderland mentioned. In your case, VBA is looking for WORKBOOKS()which is an inbuilt method. However when VBA sees WORKBOOK()which it doesn't recognize, it tries to find the SUB PROCEDUREin the module (hoping that you might have defined it somewhere) and when it doesn't find it, it pops that error. :)

然后 VBA 期望您按照 enderland 所述相应地设置变量。在您的情况下，VBA 正在寻找WORKBOOKS()哪个是内置方法。但是，当 VBA 看到WORKBOOK()它无法识别的内容时，它会尝试SUB PROCEDURE在模块中找到（希望您可能已经在某处定义了它），当找不到它时，它会弹出该错误。:)

Hope this answers your questions.

希望这能回答您的问题。

回答by enderland

You are misunderstanding the problem.

你误解了这个问题。

The following line of code is incorrect. It does not create a new workbook like you think it does. The function Workbookdoes not exist - when you try to run/compile the VBA editor complains that the function does not exist.

以下代码行不正确。它不会像您想象的那样创建新的工作簿。该函数Workbook不存在 - 当您尝试运行/编译 VBA 编辑器时，该函数不存在。

Set wbBK2 = Workbook("Book2")       'New Workbook

Change it to

将其更改为

set wbBK2 = Workbooks.add

and it will create a new workbook and save a reference in wbBK2. Alternatively you could reference an open workbook by adding an "s" to Workbookif you are not trying to make a new one:

它将创建一个新的工作簿并在wbBK2. 或者，Workbook如果您不尝试制作新工作簿，您可以通过添加“s”来引用打开的工作簿：

Set wbBK2 = Workbooks("Book2")       'New Workbook

You also will need to dimension these variables as strings eventually:

您最终还需要将这些变量作为字符串进行标注：

    Dim CurrentFile as String        
    Dim NewFileType as String
    Dim NewFile as String

    CurrentFile = ThisWorkbook.FullName
    NewFileType = "Excel Files 2007 (*.xlsx)"
    NewFile = Application.GetSaveAsFilename(InitialFileName:="Open Order Log - " & Format(Date, "dd-mm-yyyy") & ".txt", fileFilter:=NewFileType)

---

---

Another note, too - when you use

另一个注意事项 - 当您使用

Set wsWS1 = Sheets("Sheet1")        'Sheet1
Set wsWS2 = Sheets("Sheet2")        'Sheet2
Set wsWS3 = Sheets("Sheet3")        'Sheet3

You probably want to change this to reference the sheets in the other workbook

您可能希望将此更改为引用其他工作簿中的工作表

Set wsWS1 = wbBK2.Sheets("Sheet1")        'Sheet1
Set wsWS2 = wbBK2.Sheets("Sheet2")        'Sheet2
Set wsWS3 = wbBK2.Sheets("Sheet3")        'Sheet3

as it will be quite helpful to maintain the right references to these sheets, even if you add additional workbooks.

因为即使您添加了额外的工作簿，维护对这些工作表的正确引用也会非常有帮助。

回答by The_Barman

The problem you have is that you areb using OPTION EXPICIT which means all Variables mst be defined. One way to achive this and make your code nice and readable is to use Colon as line contiuation when you first define and set the object:

您遇到的问题是您正在使用 OPTION EXPICIT 这意味着必须定义所有变量。实现这一点并使您的代码美观易读的一种方法是在您第一次定义和设置对象时使用冒号作为行的延续：

Example Below:

示例如下：

Option Explicit 

Sub OpenOrderReportExport() 

    Dim wsJL As Worksheet:  Set wsJL = Sheets("Jobs List")      'Jobs List 
    Dim wsPOT As Worksheet: Set wsPOT = Sheets("PO Tracking")   'PO Tracking 
    Dim wsTNO As Worksheet: Set wsTNO = Sheets("Tel-Nexx OOR")  'Tel-Nexx OOR
    Dim wbBK2 As Workbook:  Set wbBK2 = Workbook("Book2")       'New Workbook 
    Dim wsWS1 As Worksheet: Set wsWS1 = wbBK2.Sheets("Sheet1")  'Sheet1 
    Dim wsWS2 As Worksheet: Set wsWS2 = wbBK2.Sheets("Sheet2")  'Sheet2  
    Dim wsWS3 As Worksheet: Set wsWS3 = wbBK2.Sheets("Sheet3")  'Sheet3 

    Application.ScreenUpdating = False    ' Prevents screen refreshing. 
    Dim CurrentFile as string: CurrentFile = ThisWorkbook.FullName 
    Dim NewFileType as string: NewFileType = "Excel Files 2007 (*.xlsx)" 
    Dim NewFileType as string: NewFile = Application.GetSaveAsFilename(InitialFileName:="Open Order Log - " & Format(Date, "dd-mm-yyyy") & ".txt", fileFilter:=NewFileType) 

End Sub