STRANGE problem when passing an email address to function

[Matt7272]

Hi,
I need to check the validity of email addresses (with hyperlink).

I found a nice function and it works, unless the email address has trailing spaces (they are not considered at all).

It seems to me that Excel 2019 automatically eliminates the trailing spaces when passing the email address when in reality the data in the cell is WITH trailing spaces.

Let's say I have this email address in A1: "john at gmail.com " (note the space at the far right).


In the VBA function I pass the A1 value to strEmail

If I check the lenght of the string passed into the function or the lenght of the trimmed string, they're the same.

I also checked in VBA Editor the real time values of variables and the string I pass is already without spaces.

Even removing the hyperlink from the cell doesn't change things.
How can I solve this problem?


Thank you very much for your time.

Matt

[6StringJazzer]

Welcome to the Forum Matt7272 !

There is no way to answer this without seeing the code you are using and how you are using it. Attach a file with the code and at least example you are describing with both the cell with an email address and how you are calling the function.

[Matt7272]

Hi,
here's the file.

Thank you!

[6StringJazzer]

The problem is in the function TrueTrim. It modifies the input argument, which is a poor coding practice for a function. In VBA, the default argument mode is ByRef, so the modified argument is updated in the caller. The function should really use a local variable to do this, but the easiest fix is to explicitly use ByVal.

Please Login or Register  to view this content.

[Matt7272]

Hi 6StringJazzer,
so just by removing "ByVal" it now works!
I didn't know it could have such an effect, I've never bothered to change or remove "ByVal" in any of my functions! (Not that I made a ton!
Thank you very very much!

Matt

[6StringJazzer]

I didn't remove it, I added it. ByRef is the default, so I added ByVal.

ByRef means that when the argument is referenced in the function code, it is by address. Any changes made to that argument affect the the value of the variable that was passed by the caller.

ByVal means that a copy of the argument is made to be available locally by the function code. Any changes to that argument are local to the function, and do not affect the caller.

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

[Matt7272]

Yes, sorry!
I first looked at the other function!

Thanks again very much!