Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Converting Text to Proper Case (VB/VBA 6.0)

    <P ID="edit" class=small>(Edited by MarkD on 07-Dec-04 19:11. Revised for clarity.)</P>This is an update to GetProperCase function originally posted in Access <!post=Re: force mixed case in textbox,309766>Re: force mixed case in textbox<!/post>. Revised code includes optional use of "Exceptions" table or file. Attached zip file includes 4 files: Module1.txt, Class1.txt, TextFile.txt, TestFile.txt. To test code, rename first two files as "Module1.bas" and "Class1.cls" and import into VB/VBA project. The other two files are sample "Exception" text files that can be used as data source. The functions used to convert text to proper case are found in Module1. The ConvertToProperCase function converts a single item (word or phrase) to proper case; the optional second parameter specifies whether or not to compare the text against an exception string generated by the class module. Example:

    <code>? ConvertToProperCase("macdonald", True)</code>
    <code>MacDonald</code>

    <code>? ConvertToProperCase("MCDONALD")</code>
    <code>McDonald</code>

    The ConvertToProperCaseEx function converts all words in a text string to proper case, again with an optional TestExceptions parameter. Example:

    <code>? ConvertToProperCaseEx("mrs. john h.p. o'hara iii", True)</code>
    <code>Mrs. John H.P. O'Hara III</code>

    Before using these functions, an instance of the class, Class1, must be initialized if using the exceptions option. Example of initialization code:

    <code>Private Sub InitializeClass()</code>

    <code> Set obj = New Class1</code>
    <code> With obj</code>
    <code> ' Specify path, file, delimiter, initialize exception string:</code>
    <code> .SourcePath = "C:PROGRAM FILESTEMP"</code>
    <code> .SourceFile = "TEXTFILE.TXT"</code>
    <code> .Delimiter = S_DELIMITER</code>
    <code> .GetNewExceptionString</code>
    <code> End With</code>

    <code>End Sub</code>

    The two functions can be used w/o initializing instance of Class1, if you do not need to test for exceptions (specify "False" for second parameter, or omit). The reasons for using class module were, one, to encapsulate the code that generates exception string - some alternative method could be used, w/o impacting use of functions in Module1 - a text file (one word or phrase per line) seemed like simplest approach. Secondly, to avoid repeatedly opening and closing a recordset to generate exception string, which would not be very efficient. Once an instance of the class is initialized, the ExceptionString property can be retrieved as often as needed w/o opening new recordset. The GetNewExceptionString function in class module allows user to specify when a new exception string should be generated, as shown above. Also note, the path and file name of the text file to be used are specified first. The sample code uses "pipe" character (|) as default delimiter (S_DELIMITER constant) since it is unlikely to be used in the actual text being passed to function. For more details, see attached files.

    Note: Code requires set project reference to "Microsoft ActiveX Data Objects" (ADO) 2.X Library. (ADO methods are used to get data from text file.) Sample code includes some test procedures, test msg's, etc which can be commented out or deleted in actual use. Code worked as expected in standard VB 6.0 .Exe project as well as in Office VBA.

  2. #2
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Text to Proper Case (VB/VBA 6.0)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    At first glance this seems to be much more sophisticated and much better than the simple things I've come up with!

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Converting Text to Proper Case (VB/VBA 6.0)

    Thanx, hope this may be useful....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •