vba 如何从excel调用VBA函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13886383/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call VBA function from excel
提问by Husnain Kazmi
I have en excel file where i have to put validation rule. I have one cell let says "customer Time" where user can enter time but it is customize time. User can enter time like that
我有一个 excel 文件，我必须在其中放置验证规则。我有一个单元格，上面写着“客户时间”，用户可以在其中输入时间，但它是自定义时间。用户可以这样输入时间
23:45
98:20
100:30
User cannot enter string and no special character except colon. I have made one macro and it works perfectly accoriding to customer demand. Here is macro
用户不能输入字符串，也不能输入除冒号外的特殊字符。我制作了一个宏，它可以完美地满足客户的需求。这里是宏
Public Function isValidTime(myText) As String
Dim regEx
Set regEx = New RegExp 'Regular expression object
regEx.Pattern = "^[0-9]+([:]+[0-9]+)*$" ' Set pattern.
If regEx.test(myText) Then
isValidTime = myText
Else
isValidTime = "Null"
End If
End Function
Note: To test this macro you have to go to VBA ide in Tool then reference and then select microsoft visual basic vbascript 5.5
注意：要测试此宏，您必须转到 Tool 中的 VBA ide，然后参考，然后选择 microsoft visual basic vbascript 5.5
Now i want to call this at excel. I can call like =IsValidTime("23:43") and getting result but customer is not interested to call this. Customer need a excel where he/she enter the value and value will validate according to above criteria and get the exact value or Null.
现在我想在 excel 中调用它。我可以调用 =IsValidTime("23:43") 并获得结果，但客户没有兴趣调用它。客户需要一个 excel，他/她在其中输入值，值将根据上述标准进行验证并获得确切值或 Null。
I dont know how to fix this task. I have Validated date and time as well from Data and then data validation and set the rule and it works perfect, i need the same way for my this rule as well. Any help will be highly appreciated...
我不知道如何解决这个任务。我也从 Data 中验证了日期和时间，然后进行了数据验证并设置了规则，它工作得很好，我的这个规则也需要同样的方式。任何帮助将不胜感激...
Thanks Kazmi
谢谢卡兹米
回答by Scott Holtzman
You can use the Worksheet_Changeevent inside the sheet. Inside the VBE, select the sheet and choose Workhseetfrom the left drop-down and Changefrom the right.
您可以Worksheet_Change在工作表内使用该事件。在 VBE 中，选择工作表并Workhseet从左侧下拉列表和Change右侧下拉列表中进行选择。
Enter the following code:
输入以下代码：
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A" Then 'assumes user input cell is A1
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ErrTrap
Target.Value = isValidTime(Target.Value)
End If
KeepMoving:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Exit Sub
ErrTrap:
MsgBox Err.Number & Err.Description
Resume KeepMoving
End Sub
Public Function isValidTime(myText) As String
Dim regEx
Set regEx = New RegExp 'Regular expression object
regEx.Pattern = "^[0-9]+([:]+[0-9]+)*$" ' Set pattern.
If regEx.test(myText) Then
isValidTime = myText
Else
isValidTime = "Null"
End If
End Function
