Excel自定义函数

自定义函数

假定你想要为Excel编写自定义函数，但是这个自定义函数用vba写起来比较麻烦，还有一种做法就是编写一个dll。我们准备用熟悉的C/C++来编写dll，此时我们打开VS，写下了一个自定义函数Foo，头文件定义如下

#ifndef TEST_H
#define TEST_H

#pragma once

#include <Windows.h>

#ifdef TESTDLL_EXPORT
# define TESTDLL_API __declspec(dllexport)
#else
# define TESTDLL_API __declspec(dllimport)
#endif

#ifdef __cplusplus
extern "C" {
#endif

	TESTDLL_API int WINAPI Foo();

#ifdef __cplusplus
}
#endif

#endif // !TEST_H

这里大部分都是模板代码，需要注意的是Foo的声明。Foo的calling convetion是WINAPI，也就是__stdcall，vba中自定义c函数必须是WINAPI。

我们在再来看vba中如何声明该函数

Declare Function Foo Lib "libpath" () As Long

这里有3点需要注意

1. Foo有返回值，所以声明为Function
2. libpath为dll的路径，可以是绝对路径，也可以是相对路径，这到后面再介绍
3. 返回值为Long不是Integer

C/C++导出函数名称

我们知道，c/c++编译器通常会对函数名进行修饰，其中两个原因是因为函数可以重载，还有为了区分不同的调用约定。这就导致dll中的导出函数名称和我们编写的函数名字不一样。为了能够让VBA找到导出函数，我们一般可以在声明VBA函数时指定Alias。

假设c中有如下函数

void WINAPI Foo(int i);

对于32位和64位平台，导出函数名称不一样。[1]

32位

导出函数名字位_Foo@4，所以函数声明为

Declare Sub Foo Lib "libpath" Alias "_Foo@4" (ByVal i As Long)

64位

Declare PtrSafe Foo Lib "libpath" (ByVal i As Long)

注意，这里我们使用了PtrSafe关键字，因为在64位Office下面，函数声明需要PtrSafe关键字。

数据类型

下表给出VBA和C/C++中常见的数据类型的对应关系

VBA	C/C++	说明
Boolean	VARIANT_BOOL	vba中的True=-1，False=0 VARIANT_BOOL实际为short的typedef
Integer	short
Long	int
Double	double
String	BSTR	BSTR是OLE的数据类型，是一个带长度前缀的字符串
Variant	VARIANT
Array	SAFEARRAY

String

vba中的String是Unicode string，对于常见的Win32 API，通常接受LPCWSTR和LPCSTR两种字符指针，分别是utf-16和ANSI的字符串。

String -> LPCWSTR、const wchar_t*或BSTR

这种比较简单，我们使用StrPtr函数，直接获取String的指针即可，因为String本质上是一个BSTR。

Declare Sub Foo Lib "libpath" (ByVal str As Long)

void WINAPI Foo(BSTR);
// void WINAPI Foo(LPCWSTR);
// void WINAPI Foo(const wchar_t*);

注意，vba的文档中找不到StrPtr。

String -> LPCSTR 或 const char*

String是utf-16编码的，如果我们声明vba中的函数接受String，且c中的函数接受const char*，那么vba就会根据系统当前的locale，将utf-16转换为ANSI字符串。显然这个过程可能是有损的，通常不建议这么做。还有中方法就是再vba中将字符串转换为utf-8数组后，再传递给c函数。

Declare Sub Foo Lib "libpath" (ByVal str As String)

void WINAPI Foo(const char*);
// void WINAPI Foo(LPCSTR);

ARRAY

要传递vba的数组到c/c++函数中，可以使用以下两种方法[2]

指向第一个元素的指针

Declare Sub Foo Lib "libpath" (ByRef anArray As Long, ByVal size As Long)

Dim anArray() As Long
Foo anArray(0), UBound(anArray) + 1

void WINAPI Foo(const int* anArray, int size);

SAFEARRAY指针

Delcare Sub Foo Lib "libpath" (ByRef anArray() As Long)

Dim anArray() Long
Foo anArray

void WINAPI Foo(SAFEARRAY** ppArray);

vba函数命名

vba函数的名字不能同Excel中已经存在的名字相冲突，比如单元格的名字。所以F2这样的名字是非法，在调用时你会得到#REF!错误。

Dll路径

Declare时我们需要指定dll的路径，显然我们不想要硬编码路径。一种方法是我们修改PATH变量，加入dll所在的目录。这个动作可以在打开插件的工作簿时完成，代码如下

Private Declare PtrSafe Function SetEnvironmentVariableW Lib "Kernel32" (ByVal name As LongPtr, ByVal value As LongPtr) As Long

Private Sub Workbook_Open()
	Dim newPath As String
	newPath = "path to dll" & Environ("PATH")
	' TODO Warn if changing PATH fails
	Debug.Print SetEnvironmentVariableW(StrPtr("PATH"), StrPtr(newPath))
End Sub

References

[1] Decorated names
[2] How To Pass Arrays Between Visual Basic and C