SearchBox Week, Day 1

3 Comments:

1. Peder Schmedling writes:
   December 16, 2008 at 2:41 am

   Great post JP, I'm looking forward to get a peak into what people are searching for.

   In order to improve user-friendliness of the rngAlign function I would suggest to enumerate the valid alignment options. This way the programmer using the function would get access the valid options when using the function through intellisense.

   Also I can't see why you need the line

   If rRng Is Nothing Then GoTo ErrorExit

   This problem would be handled by the error handling when the With statement is executed anyway..?

   I took the liberty of formalizing the code a bit to show how I personally would implement a function to provide essentially the same functionality:

   'Enumerate available Vertical alignements
   Public Enum vacVerticalAlignement
       vacTop = xlTop
       vacCenter = xlCenter
       vacBottom = xlBottom
       vacJustify = xlJustify
       vacDistributed = xlDistributed
   End Enum
   
   'Enumerate available Horizontal alignements
   Public Enum hacHorizontalAlignement
       hacGeneral = xlGeneral
       hacLeft = xlLeft
       hacCenter = xlCenter
       hacRight = xlRight
       hacFill = xlFill
       hacJustify = xlJustify
       hacCenterAcrossSelection = xlCenterAcrossSelection
       hacDistributed = xlDistributed
   End Enum
   
   '******************************************************************************
   '* Type      : Function
   '* Purpose   :
   '*  Applies a given alignement (both vertical and horizontal) to a given range.
   '* Parameters:
   '*  [In] rng, the range on which tto apply the alignement options
   '*  [In] hac, vertical alignement option (enumerated value)
   '*  [In] vac, horizontal alignement option (enumerated value)
   '* Return    :
   '*  Boolean, indicating the result of the operation
   '******************************************************************************
   Public Function SetRangeAlignement(ByRef rng As Excel.Range, _
       ByVal hac As hacHorizontalAlignement, ByVal vac As vacVerticalAlignement) _
       As Boolean
       On Error GoTo ExitProc:
   
       'Apply the alignement
       With rng
           .HorizontalAlignment = hac
           .VerticalAlignment = vac
       End With
   
       'Assume success
       SetRangeAlignement = True
   
   ExitProc:
   End Function
   

   On a side note, you may notice the use of Public scope; one should always use the smallest scope necessary, therefore most of my functions are placed in code modules with Option Private Module. This ensures that the functions are available everywhere withing my project but aren't available outside the project (except if I deliberately intend for this). Likewise in object modules I'll use the Friend scope most of the time.
2. JP writes:
   December 16, 2008 at 8:42 am

   Peder,

   I did a lot of cutting and pasting while writing the post. I left that in by mistake, and I'll remove it shortly.

   When I test your code I get "User-defined type not defined" — when I pasted the Enum sections into a class module, it worked. It's very clever and I like the approach, although I would have chosen easier to remember names than "hacGeneral". But the Intellisense makes it easier.
3. Peder Schmedling writes:
   December 16, 2008 at 3:30 pm

   Ok, I see.

   I find it strange that you receive the "User-defined type not defined" message.. This code should work regardless of module type..

   One mistake you may have made is to not keep the Enum section "in the top of the module". If you for example define a function above the Enum section, the compiler will just disregard the Enum definition totally. This is a flaw in VBA, at least in MS office products, if you ask me. The compiler should have come up with some sort of a warning or error. Essentially, keep the Enum section(s) together with module level constant and variable definitions. The probable reason it worked for you when you moved it to a separate class module must be because the Enum is global and was therefore available to the SetRangeAlignement function.

   If this doesn't solve your problem, please let me know.

   As for the naming convention, I try to conform with the Reddick VBA (RVBA) conventions (http://www.xoc.net/standards/default.asp). This is the coding convention we use at work, so normally this is the way I write code.

   Explanation: the naming of the Enum "hacHorizontalAlignement" derives from "Horizontal Alignment Constant". As for the members, for example "hacGeneral", this derives from the name of the parent Enum, "Horizontal Alignment Constant" in conjunction with the suffix "General" describing the member itself (the same logic applies for the vertical alignement Enum). This may seem comprehensive at first, but as you get used to it, it's very helpfull. And as you say, intellisense makes it easier