Quick ByRef question 1

[jtseltmann]

I have a public function that processes some data manipulations. I wanted to create a new sub that does the "data conversions and checks". My sub is not returning the correct data and I cannot figure out why.

Here is a snippet...minus all the middle junk.
'----------------------------------------------
Public Function Import_CSV_file() as boolean
Dim varData as variant

CleanData(varData)

MsgBox varData

End function

Public Sub CleanData(varData as variant)
varData = Trim(varData)
varData = Replace(varData,",","")
End Sub
'----------------------------------------------

I had thought that if the data passed in varData was
" aaa,bbb,ccc " that when passed to CleanData it would be displayed as "aaabbbccc" but is it not. It is just as it was when it was passed to the routine.

Can anyone help me understand why? I thought I was using ByRef correctly but short of using CleanData as a function I don't know why? Isn't that the point of ByRef..?

Thanks for any insight

 

[Remou]

You can either include Call, or leave out the brackets:

Call CleanData(varData)
CleanData varData

 

[TheAceMan1]

How are ya jtseltmann . . .

I simulated exactly what you've posted and had [blue]no problem![/blue]

This [blue]may be due to low resources[/blue], so close all other applications and try again. If This doesn't work, corruption is a possibility.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[pbaldy]

I actually had the same problem, and I think it's because you have 2 variables with the same name, and the second function is operating on its input parameter, not the variable, which would be out of scope to it anyway. This worked:

Dim varData As Variant

Public Function Import_CSV_file() As Boolean

varData = " aaa,bbb,ccc "
CleanData

MsgBox varData

End Function

Public Sub CleanData()
varData = Trim(varData)
varData = Replace(varData, ",", "")
End Sub

Paul
MS Access MVP 2007/2008

 

[randy700]

How about...

Public [s]Sub[/s][b][COLOR=red] Function[/color][/b] CleanData(varData as variant)
   varData = Trim(varData)
   varData = Replace(varData,",","")
   [b][COLOR=red]CleanData = varData[/color][/b]
End [s]Sub[/s][b][COLOR=red] Function[/color][/b]

Randy

 

[Remou]

Perhaps this is the problem:

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist. If you use either Call syntax to call any intrinsic or user-defined function, the function's return value is discarded.

--

http://visualbasic.about.com/od/learnvbnet/a/byvalbyref.htm

 

[randy700]

Sorry, left out the other function.

Public Function Import_CSV_file() As Boolean
    Dim varData As Variant
    [b][COLOR=red]varData[/color][/b] = CleanData(varData)
    MsgBox varData
End Function

Public [s]Sub[/s] [b][COLOR=red]Function[/color][/b] CleanData(varData As Variant)
   varData = Trim(varData)
   varData = Replace(varData, ",", "")
   [b][COLOR=red]CleanData = varData[/color][/b]
End [s]Sub[/s] [b][COLOR=red]Function[/color][/b]

Randy

 

[jtseltmann]

Everyone,
Thank you for the efforts to help. Remou gets the star for figuring out my problem. I did not know that using the "Call" and or non use would change the return and it in fact does!

Thanks for all the suggestions about trying the function. I have done that and know the outcome I just needed an answer to why this viable option wasn't working for me. The code doesn't care about the variable name or scope. It was simply using the call with () e.g. "Call CleanData(varData)" or "CleanData varData".

Thanks again all...

 

[PHV]

CleanData[!]([/!]varData[!])[/!]
The above instruction call the CleanData procedure with parameter varData passed by value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886