VBA - Tips |
Code 39 BarCodes Without Corel's BarCode Wizard |
To automate the production of barcodes using CorelDraw's Wizard you must use SendKeys. This is a slow and unreliable way of creating bar codes. The "Human Readable" text associated with Code 39 bar codes from Corel's BarCode Wizard has a variable format. For some input strings the output human readable string has double spacing between the characters, although the barcode is still OK. I cannot recommend the Corel's BarCode Wizard for Code 39 fonts. Instead use http://grandzebu.net. The barcode creation is fast and reliable. There is no Human Readable text but this you can add yourself. He even provides a means to check the validity of the input string. The font is only suitable for a total of 43 characters. The characters are upper case characters, digits and the characters -, ., $, /, +, % and space. The barcode only requires an asterisk both before and after the input string and the use of the supplied font. As a result a code 39 barcode can easily be created manually in CorelDraw, Word, Excel or Access. Another font that can be used and is available on the internet is 39251.TTF. First install this font called code39.ttf that I have downloaded from http://grandzebu.net. on your computer then type in the barcode characters with asterisks at the start and end. e.g. *12358* Select the whole character string, including the asterisks, and change the font to "Code 3 de 9" and the barcode will be created. Remember this font is limited to creating barcodes from capital letters and digits only or a mixture of both. Automatic creation of Code 39 barcodes is fairly easy. Insert a string in the document with leading and trailing asterisks and change the string font to "Code 3 de 9". Do not attempt to use Corel's PrintMerge with this method as PrintMerge ignores asterisks and so the barcode will be wrong. To check a barcode simply select the barcode and change the font back to Arial and you can read the input data. Remember the input data must start and finish with an asterisk. Here is a CorelDraw X6 program that will create sequential Code39 barcodes and also display a progress meter. If you want to supply the finished barcodes in pdf form then you must convert the barcodes to curves otherwise the barcodes will not appear. In the rare instance you need a check digit. You must calculate the check digit and place it at the end before the last asterisk. There are mainly only 2 types of check digits used for Code 39. Modulo 43 and Modulo 10. Modulo 10 Modulo 10 is used on barcodes that contain only digits no letters. e.g. If the numbers are 795402 the digit on the right, 2 is multiplied by 3, the next digit, 0 is multiplied by 1, the third digit, 4 is multiplied by 3, the fourth digit 5 is multiplied by 1, the next by 3 and the next by 1 and so on. The digits from the right to left are multiplied by 3 then 1 then 3 then 1 and so on until all the digits are multiplied. The answers are the added. 2x3 + 0x1 + 4x3 + 5x1 + 9x3 + 7x1 = 57 Now since 57 cannot be divided by 10 without a remainder what is the difference between 57 and the next multiple of 10. In this case the next multiple of 10 is 60 so the difference is the check digit 3. You would then enter *7954023* as your string for the barcode. If the sum had been 50 then the check digit would have been 0. Here is a bas file you can import and run to calculate the Modulo 10 Check Digit. Modulo 43 Modulo 43 is used on barcodes that contain both digits & capital letters plus 7 additional characters. The 43 is derived from 10 numbers, 26 letters & 7 special characters that include a space. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Below is my first attempt at writing code for Modulo 43. Each character in the barcode is first converted to a modulo 43 number that is similar to an ASCII number. These modulo 43 numbers are added and the divided by 43. The remainder is then converted back to a modulo 43 character. The new character is the check digit. Sub AddCheckDigitToBarCode() Dim strBarCode As String strBarCode = "A123+B%JC5D6E71" MsgBox strBarCodeWithCheckDigit(strBarCode) End Sub Function strBarCodeWithCheckDigit(strBarCode As String) As String Dim lngCharPosition As Long Dim lngModTotal As Long Dim strChar As String 'Look at each barcode character in turn. For lngCharPosition = 1 To Len(strBarCode) strChar = Mid(strBarCode, lngCharPosition, 1) 'CharToNumber determines the Modulo 43 chracter number. 'Add the character number of each character together. lngModTotal = lngModTotal + CharToNumber(strChar) Next lngCharPosition 'lngModTotal Mod 43 divides lngModTotal by 43 and returns the remainder. 'The remainder is the number of the Modulo 43 character. 'NumberToChar(lngModTotal Mod 43) converts the Modulo 43 character number to the check digit character. 'Then attached to the end of the barcode to get the barcode with the check digit. strBarCodeWithCheckDigit = strBarCode & NumberToChar(lngModTotal Mod 43) End Function Function CharToNumber(strChar As String) As String 'Determinee number that represents modulo 43 chracters. Dim RefNo As Long Select Case Asc(strChar) Case 97 To 122 'Convert a lower case character to uppercase. strChar = StrConv(strChar, vbUpperCase) End Select Select Case Asc(strChar) Case 48 To 57 RefNo = Asc(strChar) - 48 Case 65 To 90 RefNo = Asc(strChar) - 55 Case 32 RefNo = 38 Case 36 RefNo = 39 Case 37 RefNo = 42 Case 43 RefNo = 41 Case 45 RefNo = 36 Case 46 RefNo = 37 Case 47 RefNo = 40 Case Else MsgBox "You have an illegal character in your barcode." End End Select CharToNumber = RefNo End Function Function NumberToChar(Nos As Long) As String 'Determine the modulo 43 chracter the represents the chracter number. Dim strChar As String Select Case Nos Case 0 To 9 NumberToChar = Chr(Nos + 48) Case 10 To 35 NumberToChar = Chr(Nos + 55) Case 36 NumberToChar = "-" Case 37 NumberToChar = "." Case 38 NumberToChar = " " Case 39 NumberToChar = "$" Case 40 NumberToChar = "/" Case 41 NumberToChar = "+" Case 42 NumberToChar = "%" End Select End Function A few days later I realised there was a more compact way of writing the code. It did not need ASCII numbers but instead it uses the function InStr to determine the number of a character. It has plenty of comments that explains what it is doing. You can remove the comments if you like. Sub BarCodeCharacters() Dim strBarCodeCharacters As String 'Input a barcode string. strBarCodeCharacters = "BWT5Q" MsgBox "New BarCode String with Check Digit Character " & Modulo43(strBarCodeCharacters) End Sub Function Modulo43(strBarCodeCharacters As String) As String Dim lngCharacterPosition As Long Dim lngCheckDigitRefNo As Long Dim lngCounter As Long Dim strAllChars As String Dim strChar As String * 1 'This is a single character string. Dim strCheckDigit As String Dim lngMod43 As Long 'Here is the complete list of allowed characters. strAllChars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%" 'Each character has a reference number starting at 0 and going through to 42. For lngCounter = 1 To Len(strBarCodeCharacters) 'Look at each character in the barcode string. strChar = Mid(strBarCodeCharacters, lngCounter, 1) 'InStr(strAllChars, strChar) finds the position of the character within strAllChars. lngCharacterPosition = InStr(strAllChars, strChar) 'If the character is found within then it will have position greater than zero. If lngCharacterPosition > 0 Then 'Convert the Character Position to a Reference Number. 'Because the Character Reference No's start from 0 and not 1 the Reference Number is 1 less than the Character Position. 'For instance if the character was A, its position is 10th. 'So A's Reference No is 9. 'Add the Reference No's. lngMod43 = lngMod43 + lngCharacterPosition - 1 Else 'If the character was not found the character was not one of 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+% so the character is illegal. MsgBox "You have an illegal character in your barcode." & vbCr & "This macro will now stop." End End If Next lngCounter 'Determine the Check Digit Reference Number. lngCheckDigitRefNo = lngMod43 Mod 43 'Convert the Reference Number to a Check Digit Character remembering to add 1 to convert from Reference Number to Position. strCheckDigit = Mid(strAllChars, lngCheckDigitRefNo + 1, 1) 'Return the BarCode characters with the Check Digit. Modulo43 = strBarCodeCharacters & strCheckDigit End Function Here is the new method, as shown above, as a bas file. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Issue date 2022_02_24