Trouble passing variable to another function

**Jasmith** · 04-27-2011, 01:31 PM

Hello Everyone,

Currently my main function is looping through the rows in the worksheet and making changes to different columns as it iterates through all of the data. In one column I have a telephone that is in a format I want changed:

(NPA) NXX-XXXX corrected to NPANXXXXXX
I.E. (408) 555-1212 to 4085551212

What I am trying to do is use another function that will chop up the telephone number and return the "corrected #" and then save the new value to the cell in the corresponding row. The problem I run into is when I compile the code I get

"Compile Error:

ByRef argument type mismatch"

Below is a sample of the code I am using

Please Login or Register  to view this content.

I have tried to modify my second function and change how the variable is being handled and the problem persists.

Please Login or Register  to view this content.

I am sure it is something simple I am missing .. any idea's would be helpful.

Thank you everyone for your time,

Jason S

**tigeravatar** · 04-27-2011, 01:55 PM

Jason,

I think the problem is right here in the first part of the code:

Please Login or Register  to view this content.

You're trying to pass the variable before defining it. Also, you're defining it backwards. Instead of that, try this:

Please Login or Register  to view this content.

**Brown207** · 04-27-2011, 02:09 PM

Here's a working version of your code:
It was missing more than just the CTNasNumber definition

Doesn't seem your len function was formatted correctly (parenthesis in the wrong place). And I don't know about your mid function doing its job correctly, but anyway...

Please Login or Register  to view this content.

Need help with your reformatting functionality (mid formulas)?

**Brown207** · 04-27-2011, 02:12 PM

Nevermind...

It works as long as every entry in column 3 is formatted "(NPA) NXX-XXXX", otherwise it destroys it

**Jasmith** · 04-27-2011, 04:02 PM

Brown207,

TY for your help .. I forgot to mention in my first post that I use the var "RowNumber" as a global variable, this way I can use it throughout the script without having to use keep DIM'n it over and over in each function. I reworked your updated code into my function and it now looks like this.

Please Login or Register  to view this content.

Here is one thing I that I DON'T understand. Using the code you corrected I noticed that you declared 'CTNasNumber as string' inside the function "Prep_TestCCName".

Where I am stumped is I have another procedure inside the same module where I am doing the same basic thing, I have a function call w/ a variable I am passing back and forth .. and that works. My exception is that I do NOT declare "vlParCC" inside the Function "Prep_vLookUp_PartialCC". Using your fix I had to declare CTNasNumber .. why is my function "Prep_vLookUp_PartialCC" working WITHOUT having to declare vlParCC ??

Please Login or Register  to view this content.

**Brown207** · 04-27-2011, 05:33 PM

I'm guessing its because you declared vlParCC as a variant in your called function where as you declared CTNasNumber as a string. Variant is the default variable type when not declared...

I tested this in your previous function by changing the CTNasNumber to a Variant and removing its declaration and it worked fine without it. VBA was picking up the mismatch conflict before when it compiled because it was automatically assigning CTNasNumber as a Variant when it came to it without prior declaration and then found a conflict in the called function when it later declared it as a string.