Excel VBA Function Parameter Overwritten

  • Thread starter Thread starter Flystar
  • Start date Start date
F

Flystar

I have two vba functions in an excel macro, where the result of th
first is passed into the other as a parameter. The problem is that th
value of this paramter appears to be changed by the actions of thi
second function, hence overwritting the value of my first function
result. To illustrate:

Dim a, b As Integer

a = funcone(0,"Hello")
b = functwo(a,"Hello")

FUNCTION DEFINITIONS
Function funcone(pos As Integer, text As String) As Integer
Dim pos As Integer
'Some tasks obtaining pos
funcone = pos
End Function

Function functwo(pos As Integer, text As String) As Integer
For pos = 1 To 10
'Some tasks
Next pos
functwo = pos + 30
End Function

By using the basics of the functions above, a = 10 always. Why
:confused:

Tracing the values of a and b.
a = funcone(0,"Hello")
'a = 5 for example

b = functwo(a,"Hello")
'a = 10
'b = 40

This is driving me crazy so any help will be greatly appreciated
 
I have two vba functions in an excel macro, where the result of the
first is passed into the other as a parameter. The problem is that the
value of this paramter appears to be changed by the actions of this
second function, hence overwritting the value of my first functions
result. To illustrate:
Click to expand...

perhaps the following helps you:

Sub CallFunc()
Dim a As Integer, b As Integer

MsgBox a & vbCr & b
a = funcone(0, "Hello")
MsgBox a & vbCr & b
b = functwo(a, "Hello")
MsgBox a & vbCr & b
End Sub

Function funcone(ByVal pos As Integer, text As String) As Integer
funcone = pos
End Function

Function functwo(ByVal pos As Integer, text As String) As Integer
For pos = 1 To 10
'Some tasks
Next pos
functwo = pos + 30
End Function

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
 
Your suggestion was the first thing I tried.


a = funcone(0, "Hello")
MsgBox a displays "5"

b = functwo(a, "Hello")
MsgBox a displays "10"
MsgBox b displays "40"

The actual value of a is changed by calling functtwo. I have traced i
to the fact that a is passed in to functwo. This code works, but is no
nice:

a = funcone(0,"Hello")
Dim temp As Integer
temp = a
b = functwo(temp, "Hello")

Any suggestions why the value of 'a' would as a result of functwo
Note, a and b are not global variables and funcone and functwo ar
defined after the sub function which calls them.

ie.
Sub main
...
a = funcone(0,"Hello")
b = functwo(temp, "Hello")
...
End Sub

Function funcone....
Function functwo etc

I need to reuse the values of a and b later on in the sub, hence m
problems with the value of a changing
 
I need to reuse the values of a and b later on in the sub, hence my
problems with the value of a changing.
Click to expand...

do you declared the argument with the keyword ByVal?

Function functwo(ByVal pos As Integer, text As String) As Integer


--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
 
Can't get any of your code to work, variables declared twice etc., but look
at this and run it

Sub TestLoopUp()
Dim a As Integer
a = 17
Debug.Print a
funcone a
Debug.Print a
functwo a
Debug.Print a
End Sub

Function funcone(pos As Integer) As Integer
pos = pos + 1
End Function

Function functwo(ByVal pos As Integer) As Integer
pos = pos + 1
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks ppl for your replies ;) . Here is my code again.

Sub main()
Dim startpos As Integer
Dim a As Integer
Dim b As Integer
Dim str As String

'I perform tasks to find str. Assume str = "Hello"
str = "Hello"

'I perform tasks to calculate startpos. Consider startpos = 1
startpos = 1

'I call my two functions
a = funcone(startpos,str)
MsgBox(a)
b = functwo(a,str)
MsgBox(a & " - " & b)

End Sub

Function funcone(nRowIndex As Integer, strText As String) As Integer
Dim i As Integer

funcone = 0
For i = nRowIndex To nRowIndex + 10
'Some conditions, which when true sets the value of funcone
If i = 5 Then 'My code will make this true
funcone = i
Exit For
End If
Next i
End Function

Function functwo(nRowIndex As Integer, strText As String) As Integer
Dim i As Integer

functwo = -1
While nRowIndex < 10
If nRowIndex = 7 Then 'My code will make this true
functwo = nRowIndex
End If
nRowIndex = nRowIndex + 1
Wend
End Function

This is what I see from running this code in my macro:
MsgBox(a) displays "5"
MsgBox(a & " - " & b) displays "10 - 7"

By changing the second function to this works:
Function functwo(ByVal nRowIndex As Integer, strText As String) As
Integer
Dim i As Integer

functwo = -1
While nRowIndex < 10
If nRowIndex = 7 Then 'My code will make this true
functwo = nRowIndex
End If
nRowIndex = nRowIndex + 1
Wend
End Function

Thanks for the suggestion for using *ByVal*. Can someone please explain
to me why I need to use this? How come nRowIndex changes 'a'?
 
You need to use ByVal, as this passes a copy of the variable to the
function, so if the function changes that copy it doesn't matter. ByRef
passes a pointer to the actual memory location of the variable, so if the
function changes it in this case, the actual variable gets changed.

ByRef is the default.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top