I've heard advice to change from User Defined Type (UDT) to a regular Class in order to overcome the limitations of UDT, such as not being able to use For Each with a UDT.
I've also heard advice to change from a regular Class to UDT to overcome the Class limitation where you can't pass things BYREF, like...
'Function:
Public Function RemoveArticle (ByRef strMovieTitle As String)
'Expected input is like "Terminator, The"
strMovieTitle = Left(... 'removes the article.
End Function
That works fine for this call:
Dim strMovieTitle As String
strMovieTitle = "Terminator, The"
RemoveArticle strMovieTitle
But not this call:
Dim objMovie As MovieClass
objMovie.strMovieTitle = "Terminator, The"
objMovie.strMovieGenre = "Sci-Fi"
InvertArticle objMovie.strMovieTitle
Even though MovieClass defines
strMovieTitle As String
I can't go changing RemoveArticle (and every simple little function like it) to take a MovieClass parameter instead of a String parameter because there are other UDTs or Classes and String Variables that also need to use RemoveArticle.
What do I do if I need to use For Each and I also need to pass ByRef? Is there a way a Class can work around the parameter problem?
(Using Excel 2010.)
Now I have understood your concern.
You simply can't take that approach to meet your goal. As Tim Williams has commented in your question, your best bet would be something like this:
Dim objMovie As MovieClass
Dim strMovieTitle As String
strMovieTitle = "Terminator, The"
objMovie.strMovieTitle = InvertArticle(strMovieTitle)
However, I see that this still does not satisfy your need.
My suggestion is as follows:
Private and expose them with Property Let and Property Get. This way you can do the modifications you want to the properties either on set or on get (from within the class... rather than fixing things from outside the class).Aside note, in regards to create a helper class (as someone has recommended to you): you could join into one class all those functions you use widely, such as RemoveArticle or InvertArticle. However, it requires to create an instance object every time you want to use them and, therefore, does not combine well with the recommendation I am giving to you (if you want just to simplify code). So having them in a Module as you do now is fine. Just to clarify: those recommendations they gave to you are unrelated to your question here.
Example A: on set
In you class MovieClass, rename first all the instances of strMovieTitle to pStrMovieTitle and add this to your code:
Private pStrMovieTitle As String
Public Property Let strMovieTitle (strIn As String)
pStrMovieTitle = InvertArticle(strIn)
End Property
Public Property Get strMovieTitle As String
strMovieTitle = pStrMovieTitle
End Property
The usage would be something like this:
Dim objMovie As MovieClass
objMovie.strMovieTitle = "Terminator, The" ' the article position gets rectified on assignation
objMovie.strMovieGenre = "Sci-Fi"
'InvertArticle objMovie.strMovieTitle ' => you don't need to do this call
Example B: on get
To keep your original string as it comes, and do apply your helpers when you get the property value. That way you always preserve the original string. However, this approach will need more rework and it's only worthy in cases where you have lots of ways to use that String in different parts of your code.
Private pStrMovieTitleSource As String
Public Property Let strMovieTitle (strIn As String)
pStrMovieTitleSource = Trim(strIn)
End Property
Public Property Get strMovieTitleSource () As String
strMovieTitleSource = pStrMovieTitleSource
End Property
Public Property Get strMovieTitleRoot () As String
strMovieTitleRoot = RemoveArticle(pStrMovieTitleSource)
End Property
Public Property Get strMovieTitle () As String
strMovieTitle = InvertArticle(pStrMovieTitleSource)
End Property
Hope it helps