我可以在VBA中使用DPAPI（或类似的东西）吗？ [英] Can I use DPAPI (or something like it) in VBA?

问题描述

另一个在我的初学者系列关于VBA的问题。

我正在VBA中编写Excel加载项，加载项使用本地配置文件。

此文件需要包含远程服务的密码。

显然，将此密码存储为明文不太理想。但是我正在寻找一种可以对文本进行编码/解码的算法，因此至少不会在配置文件中看起来像明文。

我遇到了Windows DPAPI 的引用，但我不知道这是否是适用于Excel VBA的解决方案。我也不知道如何在VBA中使用这个API，因为我只是发现了使用.NET的引用。 Visual Studio不可用于这个项目。

所以这个2部分的问题是这样的：

1）如果可以从内部使用DPAPI VBA，我可以使用一个例子吗？

2）如果不可能在VBA中使用DPAPI，你有什么建议可以以一些可逆的编码方式存储文本？ / p>

解决方案必须在Excel 2003及更高版本中工作，如果重要。

再次感谢您。 p>

解决方案

解决方案必须在Excel 2003及更高版本中工作，如果重要。

对于Excel VBA，我建议使用CAPICOM库。

从这里。一旦安装完毕，请按照说明进行注册。

32位操作系统

将文件Capicom.dll从 C：\Program Files\Microsoft CAPICOM 2.1.0.2 SDK\Lib 复制到 C： \Windows\System32

下一步开始菜单 | 运行，键入

  Regsvr32 C：\Windows\System32\Capicom.dll 
  

64位操作系统

将文件Capicom.dll从 C：\Program Files（x86）\Microsoft CAPICOM 2.1.0.2 SDK\Lib\X86 复制到code> C：\Windows\SysWOW64

下一步开始菜单 运行，键入

  Regsvr32 C：\Windows\SysWOW64\Capicom.dll 
  

现在我们设置在我们的VBA项目中使用它

将此代码粘贴到模块中

  Option Explicit 
 
子样本（）
 Dim TextToEncrypt As String，EncryptedText As String 
 Dim TextToDeCrypt As String，DeCryptedText As String 
 Dim KeyToEncrypt As String 
 
 TextToEncrypt =Hello World
 KeyToEncrypt = JoshMagicWord
 
 EncryptedText = EncryptString（TextToEncrypt，KeyToEncrypt）
 DeCryptedText = DecryptString（EncryptedText，KeyToEncrypt）
 
 Debug.Print字符串& TextToEncrypt& 加密后看起来像这样
 Debug.Print----------------------------------- ------------------------------
 Debug.Print EncryptedText 
 Debug.Print--- -------------------------------------------------- ------------
 Debug.Print解密后的上述字符串看起来像这样
 Debug.Print------------ -------------------------------------------------- ---
 Debug.Print DeCryptedText 
 
 End Sub 
 
公共函数EncryptString（strText As String，ky As String）As String 
 Dim Cap作为对象
 Dim cryptIt 
 
设置Cap = CreateObject（CAPICOM.EncryptedData）
 
 Cap.Algorithm = 3 
 Cap.SetSecret ky 
 Cap.Content = strText 
 EncryptString = Cap.Encrypt 
结束函数
 
公共函数DecryptString（strText As String，ky As String）As String 
 Dim Cap作为对象
 Dim cryptIt 
 
设置Cap = CreateObject（CAPICOM.EncryptedData） 
 
 Cap.Algorithm = 3 
 Cap.SetSecret ky 
 Cap.Decrypt strText 
 
 DecryptString = Cap.Content 
结束函数
  

函数 EncryptString 加密字符串和函数 DecryptString 解密字符串。当您运行上述子样本

another in my beginnerish series of questions about VBA.

I am in the process of writing an Excel add-in in VBA, and the add-in uses a local configuration file.
This file needs to contain a password for a remote service.
Obviously, it is less than ideal to store this password as plaintext. But I am looking for an algorithm that can encode/decode text so it at least doesn't look like plaintext in the configuration file.

I came across a reference to Windows DPAPI but I'm not sure whether this is an appropriate solution for Excel VBA. I also am not sure how I can use this API from within VBA, as I've only found references to using it with .NET. Visual Studio is unavailable to this project.

So the 2-part question is this:
1) If it is possible to use DPAPI from within VBA, can I have an example of its use?
2) If it is not possible to use DPAPI in VBA, do you have any suggestions for how to store text in some encoded fashion that is reversible?

The solution must work in Excel 2003 and later, if it matters.

Thank you once again.

解决方案

The solution must work in Excel 2003 and later, if it matters.

For Excel VBA, I suggest using the CAPICOM Library.

Download the file from here. Once it is installed, follow these instructions for registering the Dll.

32 bit OS

Copy the file Capicom.dll from the C:\Program Files\Microsoft CAPICOM 2.1.0.2 SDK\Lib to C:\Windows\System32

Next on Start Menu | Run , type this

Regsvr32 C:\Windows\System32\Capicom.dll

64 bit OS

Copy the file Capicom.dll from the C:\Program Files (x86)\Microsoft CAPICOM 2.1.0.2 SDK\Lib\X86 to C:\Windows\SysWOW64

Next on Start Menu | Run , type this

Regsvr32 C:\Windows\SysWOW64\Capicom.dll

Now we are set to use it in our VBA Project

Paste this code in a module

Option Explicit

Sub Sample()
    Dim TextToEncrypt As String, EncryptedText As String
    Dim TextToDeCrypt As String, DeCryptedText As String
    Dim KeyToEncrypt As String

    TextToEncrypt = "Hello World"
    KeyToEncrypt = "JoshMagicWord"

    EncryptedText = EncryptString(TextToEncrypt, KeyToEncrypt)
    DeCryptedText = DecryptString(EncryptedText, KeyToEncrypt)

    Debug.Print "The string " & TextToEncrypt & " after encryption looks like this"
    Debug.Print "-----------------------------------------------------------------"
    Debug.Print EncryptedText
    Debug.Print "-----------------------------------------------------------------"
    Debug.Print "The above string after decrypting looks like this"
    Debug.Print "-----------------------------------------------------------------"
    Debug.Print DeCryptedText

End Sub

Public Function EncryptString(strText As String, ky As String) As String
    Dim Cap As Object
    Dim cryptIt

    Set Cap = CreateObject("CAPICOM.EncryptedData")

    Cap.Algorithm = 3
    Cap.SetSecret ky
    Cap.Content = strText
    EncryptString = Cap.Encrypt
End Function

Public Function DecryptString(strText As String, ky As String) As String
    Dim Cap As Object
    Dim cryptIt

    Set Cap = CreateObject("CAPICOM.EncryptedData")

    Cap.Algorithm = 3
    Cap.SetSecret ky
    Cap.Decrypt strText

    DecryptString = Cap.Content
End Function

The function EncryptString encrypts the string and the function DecryptString decrypts the string. See snapshot of results when you run the above Sub Sample

这篇关于我可以在VBA中使用DPAPI（或类似的东西）吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！