vba - 如何仅返回字符串中逗号后的文本

Question

我正在事件窗口标题栏中的括号之间提取文本。这部分工作得很好(感谢我之前在这里收到的一些帮助!)。现在我想创建两个单独的宏 - 一个只返回名字，另一个只返回姓氏。

我的事件窗口标题栏看起来像这样:

左边的一些文字 (HENDERSON,TOM) 右边的一些文字
(逗号后没有空格)

姓氏宏完美运行。它看起来像这样:

Sub a1LastName()
    'Extract last name of patient from title bar (between parens)
    Dim strPatientName As String
    Dim OpenPosition As Integer '(open paren marker)
    Dim closeposition As Integer '(close paren marker)
    OpenPosition = InStr(ActiveDocument.ActiveWindow.Caption, "(")
    closeposition = InStr(ActiveDocument.ActiveWindow.Caption, ")")
    strPatientName = Mid(ActiveDocument.ActiveWindow.Caption, _
        OpenPosition + 1, closeposition - OpenPosition - 1)
    Dim c As Long
    c = InStr(strPatientName, ",")
    strPatientName = Left(strPatientName, c - 1)
    Selection.TypeText strPatientName
End Sub

第二个宏与第一个相同，除了倒数第二行代码有一个“右”而不是“左”指令:

Sub a1FirstName()
    'Extract first name of patient from title bar (between parens)
    Dim strPatientName As String
    Dim OpenPosition As Integer '(open paren marker)
    Dim closeposition As Integer '(close paren marker)
    OpenPosition = InStr(ActiveDocument.ActiveWindow.Caption, "(")
    closeposition = InStr(ActiveDocument.ActiveWindow.Caption, ")")
    strPatientName = Mid(ActiveDocument.ActiveWindow.Caption, _
        OpenPosition + 1, closeposition - OpenPosition - 1)
    Dim c As Long
    c = InStr(strPatientName, ",")
    strPatientName = Right(strPatientName, c - 1)
    Selection.TypeText strPatientName
End Sub

这是我的问题:“名字”宏总是返回姓氏减去前四个字符，然后是名字，而不是简单的名字。

我能够在 Google 土地上的任何地方找到的唯一示例是专门针对 Excel 的。我已经结合了我的 VBA 手册，它们都给出了与我用来提取字符右侧的文本类似的示例。

我究竟做错了什么？

Answer 1

您可以使用 Split()从文本的逗号分隔部分创建一个数组，然后访问第一部分或第二部分:

Sub a1LastName()
    Dim strPatientName As String
    strPatientName = ParensContent(ActiveDocument.ActiveWindow.Caption)
    If strPatientName Like "*,*" Then
        Selection.TypeText Trim(Split(strPatientName, ",")(0))
    End If
End Sub


Sub a1FirstName()
    Dim strPatientName As String
    strPatientName = ParensContent(ActiveDocument.ActiveWindow.Caption)
    If strPatientName Like "*,*" Then
        Selection.TypeText Trim(Split(strPatientName, ",")(1))
    End If
End Sub

'Utility function: find and return text enclosed by ()
'   Return empty string if no () found
Function ParensContent(txt) As String
    Dim rv As String, pos As Long, pos2 As Long
    If txt Like "*(*)*" Then
        pos = InStr(1, txt, "(")
        pos2 = InStr(pos, txt, ")")
        rv = Mid(txt, pos + 1, (pos2 - pos) - 1)
    End If
    ParensContent = rv
End Function