Changing a Type to a Class causes ByRef parameters to act ByVal

user_6770954 0
Viewed 87

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.)

user-defined-types pass-by-reference vba class
1 Answers

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:

  • make your object internal, target properties 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

user_43523060
Related