VBA programming: Tips and tricks for scripting and Unicodes

With Visual Basic For Applications (VBA), many IT professionals have probably embraced a kind of hate love. Since the 1990s Microsoft has provided a stable development and macro environment with VBA in the Office environment, apart from the small drop out in the Office version for Mac OS 2008. However, while the other languages ​​with the name addition Visual developed over the years, VBA remains in its known status. With some tricks and tricks, the VBA programmer gets even more out of the old programming dinosaur.


Uniqueness by GUID


There are sometimes the necessity, especially for the programming of own solutions, to determine an absolutely unambiguous value, which must never be generated a second time. An example, which is often quoted in the literature, is the creation of a unique customer number. In itself, quite simply, one may simply simply increase the number table for the customer data in the database by a counter, and already one has its next customer number. This works, however, only if the database itself is linked to the database itself. However, if a salesperson wants to generate a new number on his laptop without a connection to the corporate network, this is not possible. Instead of customer numbers, the example could also be document numbers or transaction IDs. No matter what the developer wants to do with the ID, it must be unique.


Unicode files


The time, the runtime or the MAC address of the network card, which should always be unambiguous, at least according to the gray theory, are a suitable basis for calculating such a value. A look at the Windows Registry shows that there are a very large number of such GUIDs (Globally Unique Identifier) ​​per installation. Fortunately, the VBAP programmer hardly has to deal with the theoretical fundamentals when creating a GUID, as long as it has the appropriate Windows API function. When creating a GUID using the API function CoCreateGuid, Microsoft says, it is really ensured that this GUID exists only once. The 16-byte number is generated by the network card MAC and even if the user tries to create GUIDs over several years with the same network card, there is no double number. Embedded in a Microsoft Excel file with a button button1, the following code is


Private Declare Function CoCreateGuid _Lib "OLE32.DLL" _ (lpGUID As Long) _ As Long Private Declare Function StringFromGUID2 _Le "OLE32.DLL" _ (lpGUID As Long, ByVal lpszSTring As Long, ByVal lMax As Long) _ As Long Sub Button1_Click () For a = 1 To 30 For b = 1 To 6 Cells (a, b) .Value = CreateGUID NextNext End Sub Public Function CreateGUID () As String Dim lngGUID (1 To 1) As Long CoCreateGuid lngGUID 1) CreateGUID = String (38, 0) StringFromGUID2 lngGUID (1), _ StrPtr (CreateGUID), 39 End Function


If you look up the word Unicode on Wikipedia, you will be pleased with the explanation about the task "that s long-term for every meaningful character or text element of all known text cultures and character systems a digital code is fixed". At the moment, the one with the meaningful characters is rather in the form that from a per se readable file suddenly only gibberish can be taken. Because the currently defined 110.182 characters in the version 6.2 of Unicode do not really help. In 1988, Joseph D. Becker of Xerox was still of the opinion that 16-bit character encoding with 16,384 possible characters could easily encode all the characters in the world, since he had probably been guilty.


Anyone who has opened text files using OpenTextFile in the File- SystemObject in this form


Option objSHELL, objSHELL, objSX, objSX, objSX, objSX, objSX, objSX, objSX, objSX, OpenTextFile (strFILENAME, 1.0) strDATA = objOTX.ReadAll msgbox (strData)


In the Msgbox, only cryptic characters are displayed, instead of a usable text as soon as it is a Unicode file. In the UNICODE environment, with files for all imaginable characters, also for historical and no longer used languages, opening using the conventional ASCII variant does not work flawlessly. By a small adaptation of the script, which can also be used directly as a VBS file, the IT professional also opens UTF-16 files. UTF16 is the oldest and most common Unicode encoding format.


Option explicitDim objSTREAM, strDATA, strFILENAME strFILENAME = "PATHDATE NAME" Set objSTREAM = CreateObject ("ADODB.Stream") objSTREAM.CharSet = "utf-16" objSTREAM.Open objSTREAM.LoadFromFile (strFILENAME) strDATA = objStream.ReadText () Msgbox (strDATA)


Due to the structure of UTF-16, it is a very interesting phenomenon. If a UTF-16 encoded text is interpreted as ASCII, Latin letters are recognizable, but separated by zero bytes. In fact, there is a doubling of the disk space needed in our space.


But how can you find out in which encoding a file exists? There is a large number of discussion forums on the Internet, as programs can surely determine whether it is a Unicode encoding or an ASCII file. The most common variant is checking the first three bytes of a file, even if this form does not identify a Unicode file with absolute security. In our tests it has always worked.


Function InFileNum = String (100, "") InFileNum = FreeFile Open path For Binary Access Read Lock Read () () () () () () () () () () If InFileNum is set to a value of 1, (ByteOne = 255 And byteTwo = 254) Then TestFile = "Unicode" ElseIf (byteOne = 254 And byteTwo = 255) Then TestFile = "Unicode" ElseIf (byteOne = 239 And byteTwo = "ElseTestFile =" AscII "End If End Function


We have all three commands, the TestFile function is called in the form MsgBox (TestFile (path and name)), it is embedded in a Microsoft Excel file. These can be found on the Internet for downloading under PC Magazine Professional. This saves typing. Anyone who wants to further interpret files in VBA, such as Excel, will use these three scripts to ensure that the text files are always opened correctly, regardless of their encoding.