如何解决Office 2007中VBA加载项‘编译错误：预期Sub或Function’

我开发了一款兼容MS Office 2007、2010及2013版本的Excel加载项（含自定义UI），模块代码如下：

Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Const VK_CONTROL As Integer = &H11
Sub A_Data_Convert(control As IRibbonControl)
Call A_Data_Convert1
End Sub

Function A_Data_Convert1()
Dim rng As Range
For Each rng In Selection.Cells
If rng.Value <> "" Then
rng.Value = Chr(34) & "data" & Chr(34) & ": {" & Chr(34) & "text" & Chr(34) & ": " & Chr(34) & (rng.Value) & Chr(34) & "}"
End If
Next
End Function

在MS Office 2007中运行时出现编译错误：预期Sub或Function。

错误原因 #

Office 2007仅支持32位环境，而PtrSafe是64位Office（2010及以后版本引入）专属的API声明关键字，2007无法识别该关键字，直接触发编译失败。

解决方案 #

通过条件编译指令实现32位（2007）和64位（2010+）Office的兼容，修改后的完整代码如下：

#If VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
    Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Integer) As Integer
#End If

Const VK_CONTROL As Integer = &H11

Sub A_Data_Convert(control As IRibbonControl)
    Call A_Data_Convert1
End Sub

Sub A_Data_Convert1() ' 原Function无返回值，改为Sub更符合编码规范
    Dim rng As Range
    For Each rng In Selection.Cells
        If rng.Value <> "" Then
            rng.Value = """data"": {""text"": """ & rng.Value & """}" ' 用双引号简化字符串拼接，提升可读性
        End If
    Next
End Sub

额外优化说明 #

• 原A_Data_Convert1定义为Function但无返回值，改为Sub更贴合VBA编码逻辑
• 用双引号（""）替代Chr(34)实现字符串中的引号拼接，代码更简洁易读

内容的提问来源于stack exchange，提问作者Nixs