Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Function Parameter Overwritten
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 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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Function Parameter Overwritten
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: 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Function Parameter Overwritten
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Function Parameter Overwritten
I need to reuse the values of a and b later on in the sub, hence my
problems with the value of a changing. 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Function Parameter Overwritten
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) "Flystar " wrote in message ... 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: 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? 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. --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Function Parameter Overwritten
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'? --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Function Parameter Overwritten
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) "Flystar " wrote in message ... 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'? --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 named ranges - how to use as function parameter? | Excel Discussion (Misc queries) | |||
Excel 2007 Data -- SQL Server proc or function with parameter | Excel Discussion (Misc queries) | |||
use function to change a string to function's parameter | Excel Worksheet Functions | |||
Unknown IF function parameter on amortization schedule | Excel Worksheet Functions | |||
Function parameter description | Excel Programming |