VBA-子或功能未定义 [英] VBA - Sub or Function not Defined

问题描述

我的代码不断出现Sub或Function not Defined编译错误.

I keep getting a Sub or Function not Defined compile error with my code.

我要完成的工作是通过单元格中的输入获取两个项目描述符，然后为该项目生成一个唯一的字母数字序列号，然后将其输出到屏幕上，并将其保存在另一张纸上(公认的是避难所)甚至还没有开始研究.)

What I am trying to accomplish is to take two item descriptors via input in a cell and then generate a unique alphanumeric serial number to that item and then output it onto the screen as well as save it on another sheet(which admittedly haven't even started to work on).

但是，在尝试使我的SerialGenerator函数正常工作时，我一直遇到此编译错误的问题.

However, I keep running into the issue of this compile error when trying to get my SerialGenerator function to work.

我正在尝试调用该函数以将序列号作为字符串返回，并放入名为"serial"的变量中.这样我就可以将其输出到屏幕上，然后将其存储在另一张纸上.

I am trying to call the function to return the serial number as a string and place into a variable named "serial" so that I can output that onto the screen and then store it on another sheet.

Public Sub GenerateSerialNumber_Click()
    
    Dim itemname As String
    Dim description As String
    Dim serial As String
    
    itemname = Range("c3").Value
    description = Range("e3").Value
    serial = SerialGenerator(finResult)
    
    Range("c21").Value = itemname
    Range("E21").Value = description
    
    
    
    MsgBox "Serial Number Generated."
    
    
End Sub
    
Function SerialGenerator(finResult)
    
    Dim result As String
    Dim myReturn As String
    
    myReturn = Text(RandBetween(0, 9999), "0000") & CHAR(RandBetween(65, 90)) & CHAR(RandBetween(65, 90))
    
    finResult = myReturn
    
    
End Function

推荐答案

• 您正在混合公式和VBA.文本， RandBetween 和 Char 是公式.它们的VBA等效项是 Format ， WorksheetFunction.RandBetween 和 Chr .
• 使 SerialGenerator 返回值更具逻辑性.
• You are conflating formulas and VBA. Text, RandBetween, and Char are formulas. Their VBA equivalents are Format, WorksheetFunction.RandBetween, and Chr.
• Much more logical to have SerialGenerator return a value.

Public Function SerialGenerator() As String
   
    With WorksheetFunction
        SerialGenerator = Format$(.RandBetween(0, 9999), "0000") & _
                          Chr$(.RandBetween(65, 90)) & _
                          Chr$(.RandBetween(65, 90))
    End With
    
End Function

然后叫...

serial = SerialGenerator

这篇关于VBA-子或功能未定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！