VBA Custom Function - Stop Input Vairable Being Updated.

Miranic,

Please upload your function.

Meantime, why are you passing a variable to a function when it is not a variable? It seems rather pointless to me. Instead of passing the so called variable to the function, make it a Dim within the function and assign a value to it - eg.

Dim val1 as Variant
val1 = 12

Alternatively you can always pass the same value by including it in the variables. So if your function is called 'fred' then it would be like this =fred(A1,13) where 13 is always the same. However unless you are sometimes going to change the 13 to another value there is no point including it in the values passed to the function.

Patrick

you must use the argument byVal when passing variables
see this link for more info

http://msdn.microsoft.com/en-us/library/ddck1z30(VS.71).aspx

If you just want to make sure that the original variable is not changed, you could pass it in by value rather than by reference (which is the default). In the function declaration, you just place the keyword ByVal in front of the variable you want to keep the same, like this::
Function tempfunc (ByVal statvar as Integer, changevar as Double) as Boolean
Any changes made by the function to the value of statvar within the function will have no effect on the original variable being passed in. The other variable(s) would still be changeable if you don't pass each in ByVal.

The difference between the two solutions given so far is whether the original asker has a value being pulled or calculated from somewhere else that needs to be passed into the function but not changed once it gets there. You can't hardcode something that isn't decided until the function is run - isn't that the point of variables? At the same time, after the function is run, something else might need to use that same value, so it needs to stay what it is while the function runs. That's the point of ByVal or whatever other method you might use to make sure that a passed-in variable doesn't change within that function.

It would be better still to debug the function and find what is changing the variable when it should not, which is why posting the actual function would be useful.

>That's the point of ByVal or whatever other method you might use to make sure that a passed-in variable doesn't change within that function.

Nope. A variable doesn't 'just' change all by itself within a function. It is made use of however you want but unless it is assigned a new value it stays exactly as it was when it was passed-in to the function. So the ByVal part plays no part in keeping the variable constant. Another function may well need to use the same variable but unless it is assigned a new value it will still be the same irrespective of how it was declared or made use of as a variable.

Patrick

I think we're actually agreeing, but the terminology is a little confusing. ByVal creates a copy of the variable, and any changes made by the function to that copy will have no effect on the original variable. In that sense, ByVal does indeed prevent the original variable from being changed.

If Sub1 calls Sub2:

Sub Sub1()
   Dim var1 As String
   var1 = "test1"
   Sub2 var1
   Debug.Print var1
End Sub

Sub Sub2(ByVal var1 As String)
  var1 = "test2"
End Sub

The Debug.Print statement will show that at the end of Sub1, var1 is still equal to "test1". Without the ByVal, var1 will be changed to "test2". So the ByVal prevents Sub2 from changing the original value of var1.

However, if Sub2 is redesigned to not change the value of the variable (removing the var1="test2" line, in this case), then ByVal would not be necessary because no change would be taking place.

I have speculated that the value might be passed in from somewhere else and needed later in the function unchanged. If the value is actually a constant, then the variable could be declared such using the Const keyword.

One other thing to note is that if the subroutine call is made slightly differently: Sub2(var1), then the variable again will act as if it is being passed by value and will not be changed by the function.

So that is four different ways to make sure that a variable does not lose an intended value:
-ByVal
-Const
-subroutine call with parentheses
-revising the called routine to eliminate the value change

It actually seems to me that passing a variable by reference and causing it to change has to be done rather deliberately.

For the original asker, does any of the suggestions given so far solve the problem? We could debate the usage of ByVal all day, but it doesn't really matter as long as you get a solution.

>...but it doesn't really matter as long as you get a solution.

Sounds right to me.

Patrick

Thanks for your help.  Much appreciated.

Miranic - Thanks for the grade - Patrick

;-)