如何从Excel调用VBA函数 [英] How to call VBA function from excel

问题描述

我有一个excel文件，必须在其中放置验证规则.我有一个单元格说客户时间"，用户可以在其中输入时间，但这是自定义时间.用户可以输入这样的时间

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

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

注意:要测试此宏，您必须先在工具中转到VBA ide，然后进行引用，然后选择Microsoft Visual Basic vbascript 5.5

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

现在我想在excel上称呼它.我可以像= IsValidTime("23:43")那样调用并获取结果，但客户对此不感兴趣.客户需要在输入值时需要一个excel，然后值将根据上述条件进行验证并获得确切的值或空值.

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.

我不知道如何解决此任务.我还从数据中验证了日期和时间，然后进行了数据验证并设置了规则，它运行得很完美，我也需要同样的方式来处理此规则.任何帮助将不胜感激...

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...

谢谢卡兹米

推荐答案

您可以在工作表内使用 Worksheet_Change 事件.在VBE内部，选择工作表，然后从左侧下拉列表中选择 Workhseet ，并从右侧选择 Change .

You can use the Worksheet_Change event inside the sheet. Inside the VBE, select the sheet and choose Workhseet from the left drop-down and Change from the right.

输入以下代码:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" 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

这篇关于如何从Excel调用VBA函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！