I am writing a simple function, such as:
我正在编写一个简单的函数,例如:
Function myFunction() As Variant
'Some rules
End Function
For the above function, is it possible to assign an Alias like you can do with API calls?
对于上面的函数,是否可以像使用API调用那样分配别名?
Obviously this isn't the proper syntax, but you get the idea:
显然这不是正确的语法,但是你可以理解
Function myFunction() Alias myFunc As Variant
'Some rules
End Function
This would allow me to use either name:
这将允许我使用任何一个名字:
Sub Test()
Debug.Print myFunction
Debug.Print myFunc
End Sub
19
I don't understand why you would need to have a VBA alias for an VBA function.
我不明白为什么要为VBA函数设置VBA别名。
API Aliases are for referring to a function or other object in a DLL, if it's "given name" can't be used in the module tat requires it.
API别名用于引用DLL中的函数或其他对象,如果在tat要求的模块中不能使用“给定名称”。
Alias —— Indicates that the procedure being called has another name in the DLL. This is useful when the external procedure name is the same as a keyword. You can also use Alias when a DLL procedure has the same name as a publicvariable, constant, or any other procedure in the same scope. Alias is also useful if any characters in the DLL procedure name aren't allowed by the DLL naming convention. (Source)
别名——指示正在调用的过程在DLL中有另一个名称。当外部过程名称与关键字相同时,这很有用。当DLL过程与公共变量、常量或相同范围内的任何其他过程具有相同的名称时,也可以使用别名。如果DLL过程名称中的任何字符不被DLL命名约定所允许,别名也很有用。(源)
...but to answer your question, sure you could create alternate name for a function, with only a slight modification to your code:
…但要回答你的问题,你当然可以为一个函数创建一个替代名称,只需要对代码做一点修改:
Your Function:
你的函数:
Function myFunction() As Variant 'Some rules End Function
Assign an alternate name:
分配另一个名字:
Function myFunc() As Variant myFunc = myFunction End Function
This would allow you to use either name:
这将允许您使用任一名称:
Sub Test() Debug.Print myFunction Debug.Print myFunc End Sub
(When I said I didn't understand why you needed to do this, I assumed you didn't have a memory like mine! Now I understand your reasoning giving multiple names to the same function!)
(当我说我不明白你为什么要这么做时,我以为你没有我这样的记忆力!)现在我明白你给同一个函数取多个名字的理由了!
What, haven't you memorized the name and syntax of all your custom VBA functions yet, on top of the 1000's of built-in functions, procedures and objects, and the seemingly unlimited list of property, method, and class names??
除了1000个内置函数、过程和对象,以及看似无限的属性、方法和类名列表之外,难道你还没有记住所有定制VBA函数的名称和语法吗?
Me neither.
我也不。
A feature that helps trigger my memory when coding in VBA is the List of Properties/Methods.
在VBA中编码时帮助触发内存的特性是属性/方法列表。
Start typing the name of a function or procedure and hit Ctrl+J, and you'll get a list of custom and built-in functions, methods, procedures, etc.**
开始键入函数或过程的名称并按Ctrl+J,您将得到自定义和内置函数、方法、过程等的列表**
You can already see the procedures in a workbook from the Macros button on the Developers tab. However, only parameter-less subroutines in modules and within a worksheet are displayed in the Macros dialog box. Functions and subroutines with parameters do not show up. The code described in this column displays all of the subroutines and functions in the workbook.
您已经可以从developer选项卡上的Macros按钮看到工作簿中的过程。但是,在“宏”对话框中只显示模块和工作表中的无参数子例程。带有参数的函数和子例程不会出现。本列中描述的代码显示了工作簿中的所有子例程和函数。
More information and complete code is available here.
这里有更多的信息和完整的代码。
By programmatically manipulating the VBA Editor (VBE), you can write code in VBA that reads or modifies other VBA projects, modules and procedures, and could be used to automate development-related tasks. This is called extensibility because it extends the editor -- you can use VBA code to create new VBA code. You can use these features to write custom procedures that create, change, or delete VBA modules and code procedures.
通过通过编程操作VBA编辑器(VBE),您可以在VBA中编写代码来读取或修改其他VBA项目、模块和过程,并可用于自动化与开发相关的任务。这被称为可扩展性,因为它扩展了编辑器——您可以使用VBA代码创建新的VBA代码。您可以使用这些特性来编写创建、更改或删除VBA模块和代码过程的自定义过程。
The amazing Chip Pearson has done it again, with detailed instructions and complete code available here for some of the fun things extensibility can do for you.
令人惊叹的芯片皮尔逊又做了一次,这里有详细的说明和完整的代码,可扩展性可以为您做一些有趣的事情。
A partial list of code on Chip's page (which is here):
芯片页面上的部分代码列表(这里):
The methods use the VBA Extensibility [library] (http://www.exceltoolset.com/setting-a-reference-to-the-vba-extensibility-library-by-code/) (reference) and require programmatic access to the VBA Project, which is a security settings in Excel's options. See Chip's page for more info.
这些方法使用VBA可扩展性[library] (http://www.exceltoolset.com/setting-a-reference-to- VBA -extensibility-library-by-code/)(引用),并要求对VBA项目进行编程访问,这是Excel选项中的安全设置。有关更多信息,请参见Chip的页面。
Chip also has great information and code available on Customizing Menus with VBA, which could have benefit in making the developer's *own * job easier.
Chip还可以在VBA的自定义菜单上提供大量的信息和代码,这可以使开发人员更容易地完成自己的工作。
A seemingly "forgotten" ability of VBA if functionality to control and even intercept built-in commands. This could also be used to benefit the developer (especially one with a poor memory!)... More information and examples from Microsoft here.
VBA似乎“被遗忘”的能力,如果功能控制甚至拦截内置的命令。这也可以用来帮助开发人员(特别是内存不足的开发人员)……更多来自微软的信息和例子。
1
Simple VBA example (no classes)
简单的VBA示例(无类)
As you are calling yourself TheNotSoGuru, try the following relative simple approach: Instead of an API like alias
definition you would have to write your alias definitions in ONE user defined alias() function.
当您调用自己的TheNotSoGuru时,请尝试以下相对简单的方法:您必须在一个用户定义的alias()函数中编写别名定义,而不是像别名定义这样的API。
Calling test procedure
调用测试程序
This shows you how to call your aliases using ONE user defined alias
function; the first argument is your alias Name as string, other arguments define possible arguments of the original functions themselves:
这将向您展示如何使用一个用户定义的别名函数调用别名;第一个参数是您的别名string,其他参数定义了原始函数本身的可能参数:
Option Explicit ' declaration head of your code module
Sub Test()
Debug.Print "0) Original Function", myFunction
Debug.Print "1) alias(""(myFunc1"")", alias("myFunc1")
Debug.Print "2) alias(""(myFunc2"")", alias("myFunc2") ' too less arguments
Debug.Print "2) alias(""(myFunc2"",false)", alias("myFunc2", False)
End Sub
Example functions
例子函数
The first example needs no argument; the second example demonstrates an incorrect as well as a correct alias function call - the original function attends the input of a boolean argument (True
or False
).
第一个例子不需要论证;第二个示例演示了一个不正确的别名函数调用——原始函数处理布尔参数的输入(True或False)。
Function myFunction() As Variant
'Some rules
'...
'return result
myFunction = "Result from myFunction"
End Function
Function myFunctionWithOneArgument(Optional ByVal b As Boolean = True) As String
'Some rules
If b Then
myFunctiOnWithOneArgument= "result from myFunctionWithOneArgument " & "okay"
Else
myFunctiOnWithOneArgument= "result from myFunctionWithOneArgument " & "without comment"
End If
End Function
=============== Example of an Alias() function
=============== =一个别名()函数的例子
You are responsible to insert your alias definitions into the alias function. It allows you even to force inputs of the correct number of arguments by raising a 450 error "Wrong number of arguments ..." via error handling. If an error occurs a messagebox displays an error message.
您负责将您的别名定义插入到别名函数中。它允许您通过错误处理产生450个错误“错误的参数数量……”来强制输入正确的参数数量。如果发生错误,messagebox将显示错误消息。
Function alias(ByVal sFunc, Optional arg1, Optional arg2, Optional arg3)
On Error GoTo oops ' error handler
Select Case sFunc & "" ' check your own aliases as string values
Case "myFunc1", "1" ' your alias Definition(s)
alias = myFunction ' return original function myFunction
Case "myFunc2", "One" ' see above
' defines if one argument is needed here:
If IsMissing(arg1) Then Err.Raise (450) ' too less arguments if arg1 is missing
alias = myFunctionWithOneArgument(arg1)
Case Else
alias = "Unknown function alias " & sFunc
End Select
EverythingOkay: Exit Function
oops:
MsgBox "Function: " & sFunc & vbNewLine & vbNewLine & _
"Error No: " & Err.Number & vbNewLine & _
Err.Description, vbExclamation, "Error - Wrong number of arguments"
Err.Clear
End Function
0
Searching Aliases via Similarity
通过相似性搜索的别名
A) Intro
一)介绍
Your Comment as of 1/22: "The issue is not that I necessarily want to call out the alias intentionally, it's an issue of forgetting what I may have named a function to begin with (ie.
verifyRange
vsverifyRng
). If I knew I was calling the alias name to begin with then I wouldn't need to call the alias. But your solution does work and it was very well thought out."你在1/22的评论中说:“问题不在于我是否一定要有意地喊出别名,而在于我忘记了一个函数的名字。”verifyRange vs verifyRng)。如果我知道我开始调用别名,那么我就不需要调用别名了。但你的解决方案确实有效,而且考虑得很周全。
Due to your example in above cited comment: As you slightly modified your initial question, I thought about an alternative solution and added it as an independant new answer:
由于你在上面引用的例子:当你稍微修改了你的初始问题,我考虑了另一个解决方案,并将它作为一个独立的新答案添加:
► You could take some advantage of using a so called
SoundEx
search to group procedure names based on a phonetic algorithm.►你可能需要一些利用使用所谓的SoundEx搜索组基于语音算法程序名称。
Method: A Soundex code identifies a set of similar sounding terms, names or ... ► procedure names. If you combine this with a loop through a VBIDE list of all existing procedures/functions (don't forget to set a reference) you are able to get the most likely alias(es) listed.
方法:Soundex代码标识一组类似的发音术语、名称或……►程序名称。如果您通过一个包含所有现有过程/函数(不要忘记设置引用)的vbe列表将其与循环结合起来,那么您将能够得到列出的最有可能的别名(es)。
Example result
例子的结果
1/1 Project(s): "VBAProject" (D:\Excel\test.xlsm)
**Project name: "VBAProject" ** (Host Project)
++SoundEx("verifyRange")="V616"
-- Found -- Procedure/Function name(s) --------- ------------------
[Prc: Sub] verifyRng in Std Module modTest1 Line#: 2/[Body: 3]
[Prc: Sub] verifyRange in Std Module modSortDict Line#: 6/[Body: 6]
Note: This method builds a condensed alphanumeric code based on the six phonetic classifications of human speech sounds (bilabial, labiodental, dental, alveolar, velar, and glottal), removing vocals and some occurences of 'H','W' and 'Y'; the code consists of the first capitalized letter and three following digits (filled with 0
if no more consonants found) . BTW origins date back to the late 1800's used for indexing American census records.
注:该方法基于人类语音的六种语音分类(双唇音、唇齿音、牙音、肺泡音、velar和声门音)构建了一个压缩的字母数字代码,去除元音和一些出现的“H”、“W”和“Y”;代码由第一个大写字母和后面的三个数字组成(如果没有更多的辅音,则填充0)。顺便说一句,起源可以追溯到19世纪后期,用于索引美国人口普查记录。
Links
链接
Find the word which I closest to the particular string? http://www.creativyst.com/Doc/Articles/SoundEx1/SoundEx1.htm#JavascriptCode https://en.wikipedia.org/wiki/Soundex
找到离特定字符串最近的单词?http://www.creativyst.com/Doc/Articles/SoundEx1/SoundEx1.htm JavascriptCode https://en.wikipedia.org/wiki/Soundex
Soundex Example
探测法的例子
To demonstrate the soundex coding, try the following example call with identical results:
为了演示soundex代码,请尝试以下示例调用,结果相同:
Sub testSoundEx()
Dim i As Integer
Dim a()
a = Array("verifyRange", "verifyRng", "vrfRanges")
Debug.Print "Proc name", "SoundEx Code": Debug.Print String(50, "-")
For i = LBound(a) To UBound(a)
Debug.Print a(i), SoundEx(a(i))
Next i
End Sub
SoundEx Function
SoundEx函数
Function SoundEx(ByVal s As String) As String
' Site: https://stackoverflow.com/questions/19237795/find-the-word-which-i-closest-to-the-particular-string/19239560#19239560
' Source: Developed by Richard J. Yanco
' Method: follows the Soundex rules given at http://home.utah-inter.net/kinsearch/Soundex.html
Dim Result As String, c As String * 1
Dim Location As Integer
s = UCase(s) ' use upper case
' First character must be a letter
If Len(Trim(s)) = 0 Then
Exit Function
ElseIf Asc(Left(s, 1)) <65 Or Asc(Left(s, 1)) > 90 Then
SoundEx = ""
Exit Function
Else
' (1) Convert to Soundex: letters to their appropriate digit,
' A,E,I,O,U,Y ("slash letters") to slashes
' H,W, and everything else to zero-length string
Result = Left(s, 1)
For Location = 2 To Len(s)
Result = Result & Category(Mid(s, Location, 1))
Next Location
' (2) Remove double letters
Location = 2
Do While Location 4
SoundEx = Left(Result, 4)
End Select
End If
End Function
Helper function called by SoundEx()
SoundEx()调用的助手函数
This helper function returns a letter code based on phonetic classifications (see notes above):
这个辅助函数返回基于语音分类的字母代码(参见上面的注释):
Private Function Category(c) As String
' Returns a Soundex code for a letter
Select Case True
Case c Like "[AEIOUY]"
Category = "/"
Case c Like "[BPFV]"
Category = "1"
Case c Like "[CSKGJQXZ]"
Category = "2"
Case c Like "[DT]"
Category = "3"
Case c = "L"
Category = "4"
Case c Like "[MN]"
Category = "5"
Case c = "R"
Category = "6"
Case Else 'This includes H and W, spaces, punctuation, etc.
Category = ""
End Select
End Function
► Solution to your issue - Example Call to get Functions by Alias
►解决你的问题——示例调用函数的别名
B) Memory issue or how to jog one's memory
B)记忆问题或如何唤起记忆
You can use the following example call to search for procedure/function aliases via Syntax listProc {function name string}
, e.g. listProc "verifyRange"
and you get a condensed list of all found aliases in the Immediate Window of your Visual Basic Editor (VBE):
您可以使用以下示例调用通过语法listProc{函数名字符串}来搜索过程/函数别名,例如listProc“verifyRange”,您可以在Visual Basic Editor (VBE)的直接窗口中获得所有找到的别名的压缩列表:
Sub Test()
listProc "verifyRange" ' possibly gets verifyRange AND verifyRng via SoundEx "V616"
'listProc "verify" ' possibly gets nothing, as SoundEx "V610" has no fourth consonant
'listProc '[ displays ALL procedures without SoundEx Filter ]
End Sub
Note: Keep in mind that the SoundEx Code (e.g. "V616" for verifyRange
) is limited to a length of four alphanumeric characters. If you are looking for "verify" only (= 3 consonants V+r+f), you would get "V610" instead without findings of "verifyRange" or "verifyRng" (V+r+f+r). In this case you should search for a pair of variants.
注意:请记住SoundEx代码(例如。“V616”适用于verifyRange,其长度限于四个字母数字字符。如果您只寻找“验证”(= 3个辅音V+r+f),您将得到“V610”,而没有发现“verifyRange”或“verifyRng”(V+r+f+r)。在这种情况下,您应该搜索一对变体。
============================= Main procedure listProc
=====================
= = = = = = = = = = = = = = = = = = = = = = = = = = = = =主过程listProc = = = = = = = = = = = = = = = = = = = = =
Sub listProc(Optional ByVal sFuncName As String)
' Purpose: display procedures using a SoundEx Filter
' Call: 0 arguments or empty argument - ALL procedures without filter
' 1 argument (not empty) - procedures found via SoundEx
' Note: requires reference to Microsoft Visual Basic for Applications Extensibility 5.3
' Declare variables to access the macros in the workbook.
Dim VBAEditor As VBIDE.VBE ' VBE
Dim objProject As VBIDE.VBProject ' Projekt
Dim objComponent As VBIDE.VBComponent ' Modul
Dim objCode As VBIDE.CodeModule ' Codeblock des Moduls
' Declare other miscellaneous variables.
Dim sProcName As String
Dim sndx As String, sndx2 As String
Dim pk As vbext_ProcKind ' proc kind (Sub, Function, Get, Let)
Dim strPK As String, sTyp As String
Dim iLine As Integer, iBodyLine As Integer, iStartLine As Integer
Dim i As Integer
Dim bShow As Boolean ' show procedure name
Dim bSoundEx As Boolean
If Len(Trim(sFuncName)) > 0 Then bSoundEx = True ' show alle procedures!
' ========================================
' Get the project details in the workbook.
' ========================================
Set VBAEditor = Application.VBE
Set objProject = VBAEditor.ActiveVBProject
' Set objProject = VBAEditor.VBProjects("MyProcject") ' 1-based, project name or item number
For i = 1 To VBAEditor.VBProjects.Count ' show name, filename, buildfilename (DLL)
Debug.Print i & "/" & _
VBAEditor.VBProjects.Count & " Project(s): """ & _
VBAEditor.VBProjects(i).Name & """ (" & VBAEditor.VBProjects(i).filename & ")"
Next i
' get SoundEx of Function name
sndx2 = SoundEx(sFuncName)
' ==================
' ? PROJECT NAME
' ==================
' objProject.Type ...vbext_pt_HostProject 100 Host-Project
' ...vbext_pt_StandAlone 101 Standalone-Project
Debug.Print "**Project name: """ & objProject.Name & """ ** (" & _
IIf(objProject.Type = 100, "Host Project", "Standalone") & ")"
If bSoundEx Then Debug.Print "++SoundEx(""" & sFuncName & """)=""" & sndx2 & """" & _
vbNewLine & "-- Found -- Procedure/Function name(s)"
' Iterate through each component (= Module) in the project.
For Each objComponent In objProject.VBComponents ' alle MODULE
' Find the code module for the project (Codeblock in current component/=module).
Set objCode = objComponent.CodeModule
' =============
' ? MODULE NAME
' =============
If objCode.CountOfLines > 0 And Not bSoundEx Then
Debug.Print " *** " & _
sModType(objComponent.Type) & " ** " & objComponent.Name & " ** "
End If
' Scan through the code module, looking for procedures.
' Durch alle Codezeilen des jeweiligen Moduls gehen
iLine = 1
Do While iLine > pk < "" Then ' ohne Declaration head
' -----------------
' Found a procedure
' -----------------
' a) Get its details, and ...
strPK = pk ' 0-Prc|1-Let/2-Set/3-Get Werte abfangen !!!
'' iStartLine = objCode.ProcStartLine(sProcName, strPK) ' here = iLine !!
iBodyLine = objCode.ProcBodyLine(sProcName, strPK) ' Zeilennr mit Sub/Function/L/S/Get
sTyp = sPrcType(objCode.Lines(iBodyLine, 1)) ' Sub|Fct|Prp
' b) Check Soundex
If bSoundEx Then
sndx = SoundEx(sProcName)
If sndx = sndx2 Or UCase(sProcName) = UCase(sFuncName) Then
bShow = True
Else
bShow = False
End If
Else
bShow = True
End If
' ==============
' c) ? PROC NAME
' --------------
If bShow Then
Debug.Print " " & "[" & sPK(strPK) & ": " & sTyp & "] " & _
sProcName & IIf(bSoundEx, " in " & sModType(objComponent.Type) & " " & objComponent.Name, "") & vbTab, _
"Line#: " & iLine & "/[Body: " & iBodyLine & "]"
End If
' -------------------------------------------
' d) Skip to the end of the procedure !
' => Add line count to current line number
' -------------------------------------------
iLine = iLine + objCode.ProcCountLines(sProcName, pk)
Else
' This line has no procedure, so => go to the next line.
iLine = iLine + 1
End If
Loop
Next objComponent
' Clean up and exit.
Set objCode = Nothing
Set objCompOnent= Nothing
Set objProject = Nothing
End Sub
3 Helper Functions to the Main procedure listProc
3辅助函数的主程序列表
These helper functions return additional information to procedures and module:
这些辅助功能向程序和模块返回附加信息:
Function sPK(ByVal prockind As Long) As String
' Purpose: returns short description of procedure kind (cf ProcOfLine arguments)
Dim a(): a = Array("Prc", "Let", "Set", "Get")
sPK = a(prockind)
End Function
Function sPrcType(ByVal sLine As String) As String
' Purpose: returns procedure type abbreviation
If InStr(sLine, "Sub ") > 0 Then
sPrcType = "Sub" ' sub
ElseIf InStr(sLine, "Function ") > 0 Then
sPrcType = "Fct" ' function
Else
sPrcType = "Prp" ' property (Let/Set/Get)
End If
End Function
Function sModType(ByVal moduletype As Integer) As String
' Purpose: returns abbreviated module type description
Select Case moduletype
Case 100
sModType = "Tab Module"
Case 1
sModType = "Std Module"
Case 2
sModType = "CLS Module"
Case 3
sModType = "Frm Module"
Case Else
sModType = "?"
End Select
End Function