Of course, if you have read up to here you are likely willing to take the plunge. How about modifying the message depending upon parameters fed to it as arguments? Like, if you need 80% to pass and else fail, why not create a message like "You have {[<80] "FAILED", "PASSED"} this test with {85}%." and let the code figure out which information to impart based on the number (85) furnished as argument?
Are you tired of seeing "There are 1 apples in the basket"? Would you prefer to see "There are no apples" in place of "There are 0 apples"? The attached code can do all of that and much, much more. I am attaching two versions of it. The workbook marked SOURCE CODE contains fully commented code and a special module for testing. It also has testing examples in its library. The other workbook has identical code minus the testing module and form and very few comments. Better start with the Source Code versions and try some of the tests.
Structurally, the code consists of three parts.
- The message library
This is a worksheet where you can enter your messages. It also contains 25 rows of help. This Help is available in both attached versions even though the worksheet will hide itself once it is deployed. Read the comments in the ThisWorkbook module to better understand this functionality. Click on the Help! button and read the Help.
Finally, study the examples. The sheet shows the message text, the function call and the resulting message - from simple to intricate. - The Message class
This code takes the message number and arguments supplied by the calling procedure, picks the message from the library and uses the parameters according to the instructions contained in the message itself. It returns a pair of strings, the message body and its caption. - The interface functions InfoBox, DataBox and MsgText
InfoBox is another name for MsgBox, DataBox replaces InputBox and MsgText returns the same strings for use in MSForms Controls like Labels or TextBoxes.
- Code: Select all
FunctionName MsgID, [Language], [Auxiliary], [Arg1], [Arg2], [Arg3, ... [Arg256]
As you see, all arguments but one are optional. InfoBox 1 will do much the same as MsgBox "Something". Astr = MsgTxt(1) will do much the same as Astr = "Something". The difference is in having the convenience of managing the strings in a library and being able to return modified strings which would otherwise require custom code to be written for each function call.
The Message class "understands" three codes.
- INS [Insert]
Message simply replaces the placeholder found in the message text (as deposited in the library) with the argument supplied by the calling procedure. - REF [Refer]
Message refers to the argument and inserts one or the other or no text into the message depending upon which value is passed as the argument to be referenced. For example, "PASSED" if the value is 80 or higher. The referenced parameter need not appear in the message.
EVAL [Evaluate]
This command conditionally replaces the parameter with a value found in the message text (as retrieved from the library), but does nothing if the condition isn't met. Where, with REF, doing nothing means that there will be no trace of its activity, for EVAL "doing nothing" means that the referenced argument is incorporated in the message unchanged. EVAL works similar to VB's Select Case function. Many conditions can be examined successively until one - or none - is found to be true. This function enables the return of "There are no apples in the basket" where the argument was 0, or "There are x apples in the basket", if the value was a number.
In order to install the functionality in your project please follow the steps laid out below. It doesn't matter whether you opt to install the commented or uncommented code. However, you may refrain from installing the Test module and the form TestMsgText. This is why these two aren't mentioned below. All other parts can be taken from either of the attached workbooks.
- Copy the worksheet ‘MsgLib’ to the target project.
Using the ‘Move or Copy’ function available from the tab’s right-click menu will ensure that the code sheet is copied along with the worksheet itself. Check the code sheet after copying to be sure. - Drag the class module ‘Message’,the code module ‘MsgMan’, and the code module ‘Utilities’ into the target project in VBE’s ‘Project Explorer’ window.
In the case of 'Utilities', you can also add the procedures into another, existing standard code module if you so prefer. - Copy all procedures from the 'ThisWorkbook' code sheet to the code sheet by the same name in the target project. You can use Ctl+C and Ctl+V.
- Check, if an event procedure ‘Workbook_Open’ already existed in the 'ThisWorkbook' module transfer the code from the procedure by that same name just pasted into the existing procedure and delete the one just pasted.
- The code module MsgMan includes the three functions InfoBox, DataBox and MsgText. Any of these that are not required in your project may be deleted.
- The Utilities module includes functions LastRow, LastColumn and SetRange. All of them are required for this project (for managing the Help in the worksheet MsgLib). However, they are also available for use by your own code. Most projects need at least two of them, and having them around will save you effort elsewhere.
- Note that the worksheet ‘MsgLib’ will hide itself when the workbook is next opened. To make it visible for admin purposes run the procedure ‘ThisWorkbook.ShowMsgLib’.
__________________________________________
15 Feb 2014 - Version 8.1.1
A procedure was added to better convert the ParamArray received by the utilities into a normal array required by the class. The effect of this modification is that the program can now handle omissions of the language and auxiliary parameters even if more paramters follow, for example, "Test", , ,"apples" is now acceptable in place of, and as well as, "Test", 0, 0,"apples". This modification affects all three interface functions InfoBox, DataBox and MsgText, but none of the rest of the code.
24 May 2014 - Version 2.0
The code tended to throw datatype errors when parameters of different datatypes. This was a nuisance because the errors were inconsistent - gone when setting out to track them down. Finally, the cause was found with variables of no declared type. They are now explicitly declared as of variant datatype. In the same context the syntax for handling ParamArrays was reviewed. The new version should be a good deal more stable.
Affected parts of the code:
- Declarations & Properties in the Message Class module
- The procedure ConvertParam in the module MsgMan
- The 3 procedures InfoBox, DataBox and MsgText, likewise in the module MsgMan