65.9K
CodeProject 正在变化。 阅读更多。
Home

VBA 还是复杂公式？

stc.reseller
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.36/5 (7投票s)

2019 年 11 月 2 日

CPOL

5分钟阅读

viewsIcon

9045

downloadIcon

200

如何通过使用 Excel 公式而不是编写 VBA 代码来保持安全？

引言

Microsoft Excel 是一种广泛使用、易于上手且有免费版本（由 OpenOffice、LibreOffice 生产）的数据处理软件，基于可编程和可定制的表格。只要电子表格中的表格是完全可定制的，用户就可以进行计算，格式化输入和输出数据（如字符串、整数、货币），还可以使用公式来帮助用户在用户填入所需数据的单元格中完成从非常简单到非常复杂的算术、逻辑以及最重要的财务计算。

什么是 Excel 公式？

公式 是计算单元格值的表达式。函数 是预定义的公式，并且已经内置在 Excel 中。让我们来看一个简单的加法公式，如下图所示：

上面我们读到了什么？

我们将两个数字相加，将它们分别输入到单元格 A1 和 A2 中。使用加法公式，Excel 将把结果显示在单元格 A3 中。

但是，如果我们想使用相同的 Microsoft Excel 电子表格软件来完成更复杂的计算，我们该怎么办？

答案很简单：我们需要将所有数据 - 输入和输出 - 放入 Visual Basic for Application (VBA) 代码中。但是怎么做呢？

Sub adding_formula()
'Mind the cell we'll ask to provide us the answer (A3)
 Range("A3").Formula = "=SUM(A1:A2)"
End Sub

每个 Excel 用户倾向于认为 VBA 代码 仅仅是 Excel 公式 的简单替代品。Excel 电子表格软件中有数百个公式，也可以将它们组合起来，以在需要使用 Excel 和提供的公式进行复杂计算时使用。VBA 代码 扩展了 Microsoft Excel 的可定制性，如果我们假设要处理外部文件和数据文件、电子邮件。VBA 代码也是实现与 API 和 Windows 功能和能力配合的最佳选择 - 因为这些额外功能无法通过公式获得。

最终，VBA 代码是考虑安全风险的最佳环境。这就是为什么我说没有哪个公式过于复杂而不能让我们将其转换为 VBA 代码，仅仅是因为 VBA 代码看起来更专业，或者它可以提供更具可读性的技术内容，即使 VBA 代码比单行公式更容易学习、编写和用术语表达，后者也能帮助我们获得所需的计算结果。

本文的重点是什么？

本文涵盖了复杂公式的术语。我们的示例通过罗马尼亚身份法提供的计算和错误检查算法，帮助我们计算社会安全号码。我们将在此使用字符串操作、条件和算术参数，所有这些都包含在一个单行公式中。

罗马尼亚的社会安全号码称为“Cod Numeric Personal”（或个人数字 ID）。它由 13 位数字组成，从左到右读取，最右边的第 13 位是校验码（或控制数字）。

背景

要了解公式的含义，请阅读：CNP - 英文读者 - https://en.wikipedia.org/wiki/Social_Security_number // 非英文读者 - https://ro.wikipedia.org/wiki/Cod_numeric_personal

简而言之

CNP（罗马尼亚社会安全号码）是一个人的唯一标识符，旨在作为访问罗马尼亚所有可用个人服务的唯一密钥。它由 13 位数字组成，每个数字都有以下含义：

CNP 的格式为：SAALLZZJJNNNC - 或，将其缩写翻译成英文：SSYYMMDDCCNNNF （其中 S - 是性别；AA/YY - 表示出生年份的最后两位数字；LL/MM - 出生月份，如 01 表示一月，02 表示二月...12 表示十二月；ZZ/DD - 出生日期，从 01 到 31；JJ/CC - 出生时的省份，从 01 到 39，布加勒斯特为 40-46，NNN - 由签发机构在其居住省份内提供的序列号，从 001 到 999；C/F - 是控制或最终数字，告诉我们 CNP 是否正确构成和计算）。

如何计算 CNP 以及如何确定我们的计算是否正确

让我们按数字分割 CNP，如下所示：

S A A L L Z Z J J N N N C

2 7 9 1 4 6 3 5 8 2 7 9 C

斜体加粗字母后面的每个数字都乘以其下方的数字，除了“C”。因此，我们有一个 12 项的总和，最后将该总和除以 11。文章顶部链接后面的工作簿中使用的示例 CNP 是1831117152458。这意味着，持有者

  • 是男性（性别数字是 1）- 我们将此数字乘以1（即，公式为 mid(B2,1,1)*2）
  • 出生于 1983 年（第 2 位和第 3 位数字是 83），出生月份是 11 月（接下来的 2 位数字是 11），出生日期是 17 日（第 6 位和第 7 位数字是 17）。
  • 居住在 Dambovita 省（按字母顺序排列的第 15 个省份）
  • 在他/她所在的省份注册为第 245 人。
  • 通过应用下面公式中的乘法和求和算法，我们得到 8 作为最终的控制数字，这证明了 CNP 的构成和计算是正确的。

Using the Code

下面的代码，由复杂公式组成，是为在会计、请愿和银行等领域处理个人数据的 Excel 用户准备的。

用于检查 CNP 是否有效的公式如下：

=IF(OR(MOD(MID(B2,1,1)*2+MID(B2,2,1)*7+
MID(B2,3,1)*9+MID(B2,4,1)*1+MID(B2,5,1)*4+MID(B2,6,1)*6+MID(B2,7,1)*3+MID(B2,8,1)*5+
MID(B2,9,1)*8+MID(B2,10,1)*2+MID(B2,11,1)*7+MID(B2,12,1)*9,11)-MID(B2,13,1)=0,
AND(MOD(MID(B2,1,1)*2+MID(B2,2,1)*7+MID(B2,3,1)*9+MID(B2,4,1)*1+MID(B2,5,1)*4+
MID(B2,6,1)*6+MID(B2,7,1)*3+MID(B2,8,1)*5+MID(B2,9,1)*8+MID(B2,10,1)*2+MID(B2,11,1)*7+
MID(B2,12,1)*9,11)-10=0,MID(B2,13,1)-1=0)),"OK","GRESIT")

历史

这是我关于使用 Excel 计算 SSN 的第一篇文章。没有此概念证明的先前版本。

© . All rights reserved.