When developing Office or Excel based applications in particular, we can get most things done by using the Office / Excel object model. Occasionally though, we need features that VBA doesn't provide. In these cases, we have to use the function calls that are available in Windows.
The functions available in the Windows API are documented in the MSDN library with terminology and samples targeted for C++ developers, which is not familiar to VBA developers.
It is common knowledge that lots of VBA developers search the web for answers to coding problems. When the solution requires the use of API calls, they copy code to their application and hope that it works, usually without understanding in detail its inner workings.
Corporate developers who use unexplored API calls in in Excel applications are very likely to cause maintenance problems, because it’s doubtful that another developer will understand what the APIs were trying to achieve.
The Ribbon Commander framework offers easy to understand properties and methods as a drop-in replacement for frequently used Windows API calls. So, you don't need to search if a Windows function exists, what arguments it takes, what constants it uses, what data type it returns and whether it is compatible with 32 or 64 bit version of Office.
The functions available in the Windows API are documented in the MSDN library with terminology and samples targeted for C++ developers, which is not familiar to VBA developers.
It is common knowledge that lots of VBA developers search the web for answers to coding problems. When the solution requires the use of API calls, they copy code to their application and hope that it works, usually without understanding in detail its inner workings.
Corporate developers who use unexplored API calls in in Excel applications are very likely to cause maintenance problems, because it’s doubtful that another developer will understand what the APIs were trying to achieve.
The Ribbon Commander framework offers easy to understand properties and methods as a drop-in replacement for frequently used Windows API calls. So, you don't need to search if a Windows function exists, what arguments it takes, what constants it uses, what data type it returns and whether it is compatible with 32 or 64 bit version of Office.
Check Internet Availability With VBA
rxFramework.IsInternetConnected [True/False]
The .IsInternetConnected property offered by the Ribbon Commander framework returns True/False and can replace the following API call:
Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef dwflags As Long,
ByVal dwReserved As Long) As Long
Private Const INTERNET_CONNECTION_MODEM As Long = &H1
Private Const INTERNET_CONNECTION_LAN As Long = &H2
Private Const INTERNET_CONNECTION_PROXY As Long = &H4
Private Const INTERNET_CONNECTION_OFFLINE As Long = &H20
Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef dwflags As Long,
ByVal dwReserved As Long) As Long
Private Const INTERNET_CONNECTION_MODEM As Long = &H1
Private Const INTERNET_CONNECTION_LAN As Long = &H2
Private Const INTERNET_CONNECTION_PROXY As Long = &H4
Private Const INTERNET_CONNECTION_OFFLINE As Long = &H20
rxFramework.FollowHyperlink Method
rxFramework.FollowHyperlink [Address of the target document]
The framework's .FollowHyperlink method is similar to the Workbook.FollowHyperlink method in VBA, but it executes much faster in a separate thread without using any cached documents.
In addition, the Ribbon Commander method can used within add-ins in Excel 2007 without having any workbook open. Read more about this issue here.
Powerpoint doesn't offer a ThisPresentation object. An active presentation should be open to use VBA's ActivePresentation.FollowHyperlink method. rxFramework.FollowHyperlink can be used in PPT add-ins without any presentation being open.
In addition, the Ribbon Commander method can used within add-ins in Excel 2007 without having any workbook open. Read more about this issue here.
Powerpoint doesn't offer a ThisPresentation object. An active presentation should be open to use VBA's ActivePresentation.FollowHyperlink method. rxFramework.FollowHyperlink can be used in PPT add-ins without any presentation being open.
Create Multiple Directories With A Single VBA Method
rxFramework.createDirTree [Full Path as String]
The problem: VBA offers the MkDir statement that allows you to create a new folder. However, if the path is a nested directory structure, the parent directories must already exist or the MkDir statement will raise an error. You will need several calls to MkDir in order to create a folder structure that is several levels deep.
Solution: You can use the framework's .CreateDirTree method with the entire folder path as an argument. Ribbon Commander will automatically create all the required intermediate directories.
Solution: You can use the framework's .CreateDirTree method with the entire folder path as an argument. Ribbon Commander will automatically create all the required intermediate directories.
Toggle Add-in User Interface Errors Programmatically
rxFramework.uiErrorsVisible [True/False]
The framework .uiErrorsVisible (Get/Set) property can enable or disable Ribbon UI error reporting programmatically. If your RIbbon application displays built-in ImageMso icons dynamically, you may get error messages for missing icons in several older MS-Office versions. To suppress these persky warnings, please set this property to False and restore its setting once your program has ended.
For more info about Show add-in UI errors, please read this article.
For more info about Show add-in UI errors, please read this article.
Download Internet Files Using VBA
rxFramework.urlDownloadToFile [urlFile, localFile]
The .urlDownloadToFile method offered by the Ribbon Commander framework aims to replace the following API call (32 & 64 bit declarations shown), if your application needs to download files from the web using VBA.
#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#Else
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If
#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#Else
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If
How To Encode Passwords In UTF16 Little Endian & Hash With SHA512
Document protection is a set of restrictions used to prevent unintentional changes to all or parts of Office documents. In Excel, Worksheet and Workbook structure passwords prevent users from modifying spreadsheets. Please note that these passwords are never stored in Excel files. Instead, it is the password hashes that are stored in the Open XML file containers.
A hash is a string of characters that uniquely identifies the data in question, much like a fingerprint identifies a person.This protection hash value shall be compared with the resulting hash value after hashing the user-supplied password using the Office default (or user defined) algorithm. If the two values match, the protection shall no longer be enforced.
The length of the sheet protection hash used until Office 2010 is just 16-bits as demonstrated in an older article. It is very easy to reverse engineer 16-bit hashes to get working passwords, even with VBA code due to the huge number of hash collisions.
In 2013, Office silently introduced security changes in order to strengthen Excel sheet & workbook structure protection. The default hashing algorithm used in Excel 2013 or later is the secure algorithm SHA512. In addition, in order to prevent dictionary attacks, Microsoft Office specifies:
The Ribbon Commander framework (version 1.1.231 or later) offers a one-liner hashing function to hash strings with VBA. Both resuts (the hash value and the optional random salt value) are returned by reference (ByRef).
Set useSaltValue to False to force the function to calculate a pseudo-random string, otherwise use True to pass a salt value of your choice. The salt value should be encoded in Base64 format. Its length as specified by the saltSize should be specified in multiples of 4, otherwise a runtime error will occur.
The hashing algorithms offered are: MD2, MD4, MD5, SHA1, SHA256, SHA384 and SHA512. Please note that passwords are encoded in UTF16 little endian and the optional salt values are encoded in Base64 before being hashed. You won't get the same results from online services that encode strings in UTF-8 or ASCII. Please refer to ECMA-376 Office Open XML file format documentation for more details.
A hash is a string of characters that uniquely identifies the data in question, much like a fingerprint identifies a person.This protection hash value shall be compared with the resulting hash value after hashing the user-supplied password using the Office default (or user defined) algorithm. If the two values match, the protection shall no longer be enforced.
The length of the sheet protection hash used until Office 2010 is just 16-bits as demonstrated in an older article. It is very easy to reverse engineer 16-bit hashes to get working passwords, even with VBA code due to the huge number of hash collisions.
In 2013, Office silently introduced security changes in order to strengthen Excel sheet & workbook structure protection. The default hashing algorithm used in Excel 2013 or later is the secure algorithm SHA512. In addition, in order to prevent dictionary attacks, Microsoft Office specifies:
- a salt value, which is a random string added to the user-supplied password before it is hashed.
- the spin count, which is the number of times the hashing function shall be iteratively run. Hashing runs using each iteration's result plus a 4 byte value (0-based, little endian) containing the number of the iteration as the input for the next iteration.
The Ribbon Commander framework (version 1.1.231 or later) offers a one-liner hashing function to hash strings with VBA. Both resuts (the hash value and the optional random salt value) are returned by reference (ByRef).
Set useSaltValue to False to force the function to calculate a pseudo-random string, otherwise use True to pass a salt value of your choice. The salt value should be encoded in Base64 format. Its length as specified by the saltSize should be specified in multiples of 4, otherwise a runtime error will occur.
The hashing algorithms offered are: MD2, MD4, MD5, SHA1, SHA256, SHA384 and SHA512. Please note that passwords are encoded in UTF16 little endian and the optional salt values are encoded in Base64 before being hashed. You won't get the same results from online services that encode strings in UTF-8 or ASCII. Please refer to ECMA-376 Office Open XML file format documentation for more details.
Demo for Ribbon Commander and Excel 2007 or later
Your Feedback Matters
|
Is there an API replacement method that you would like us to add to Ribbon Commander?
Please contact us, so we make your idea a reality! |
