Is it possible to return a value from a Function "ByVal" and not "ByRef"

[alpeshjain]

Hi,

My question might sound a bit illogical and even I never thought about something like this before I encountered a problem myself.
Let me try to explain what I am looking to do. Here is the code I have, it is only one function and there are a few others in the module but I think this should be enough to explain the problem I have.

 Function ConnectToNonZeroSuccessor(sourceSh As Worksheet, taskId As String, sucMap As Dictionary) As DictionaryDim sucRows() As Integer
Dim sucTask As String
Dim dur As Integer
Dim listOfSuc As Dictionary
            If taskId = 378 Then
                DoEvents
            End If
 sucRows = searchValueMultipleRow(sourceSh, taskId, "K:K")
 If sucRows(0) <> -1 Then
    For Each sucRow In sucRows
        sucTask = Trim(sourceSh.Cells(sucRow, 1))
        dur = sourceSh.Cells(sucRow, 4)
        If dur <> 0 Then
            If Not sucMap.Exists(taskId) Then
                Set listOfSuc = New Dictionary
                'listOfSuc.item(sucTask) = "1"
                Set sucMap.item(taskId) = listOfSuc
            End If
            sucMap.item(taskId).item(sucTask) = "1"
        ElseIf sucMap.Exists(sucTask) Then
            If Not sucMap.Exists(taskId) Then
                Set listOfSuc = New Dictionary
                Set sucMap.item(taskId) = listOfSuc
            End If
                For Each key In sucMap.item(sucTask).Keys
                    sucMap.item(taskId).item(key) = "1"
                Next key
        Else
[U]            Set listOfSuc = ConnectToNonZeroSuccessor(sourceSh, sucTask, sucMap)[/U]
            If Not sucMap.Exists(taskId) Then
                Set sucMap.item(taskId) = listOfSuc
            Else
                For Each key In listOfSuc.Keys
                    sucMap.item(taskId).item(key) = "1"
                Next key
            End If
        End If
    Next sucRow
    If sucMap.item(taskId) Is Nothing Then
        MsgBox sucTask & " Does not have a non zero successor"
        Exit Function
    End If
[U]    Set ConnectToNonZeroSuccessor = sucMap.item(taskId)[/U]
 End If
End Function

So as you can see, I am trying to create kind of a map and for that I am using dictionary and as item of dictionary I have another dictionary.
At the first underlined line you can see that I am making a recursive call to same method until one of first 2 if conditions are true.
Once one of them is true, it returns a dictionary object to caller (second underlined line).
The returned dictionary object is then either first modified and then added as a value to associated with another key or directly added as a value associated with another key.
Here comes the problem, if the dictionary object is first modified then the changes are also reflected in the original dictionary which is already stored as a values associated with another key.

So my conclusion is, the issue is happening because the function return the reference of Dictionary object and not a copy/value. So when the object is modified, it also modifies the original Object.

Now I want to understand if there is way to return a copy/value of object rather than reference.

PS: I have tried my best to explain the problem, but I know it can be a bit tricky, so if you have any confusion please let me know and I can clear them.

Thanks

 

[StephenCrump]

So as you can see, I am trying to create kind of a map and for that I am using dictionary and as item of dictionary I have another dictionary.

It's difficult from this description to understand what you're trying to do here, i.e. what do you want the function ConnectToNonZeroSuccessor ultimately to return? Perhaps you could give us some more detail?

I am guessing that you want the function to create a dictionary of the values in column A where column K matches taskID? But I have no idea why you're creating multiple directories, or why you're calling ConnectToNonZeroSuccessor recursively?

 

[alpeshjain]

I am guessing that you want the function to create a dictionary of the values in column A where column K matches taskID? But I have no idea why you're creating multiple directories, or why you're calling ConnectToNonZeroSuccessor recursively?

Thanks for responding Stephen, at last got one response . It would be a bit difficult to explain the entire requirement as it is part of bigger module. But let me try a shorter version. Column A contains task Ids, columns D contains duration (0 to any) and column K contains a list of predecessors task Ids(Which are also there in column A).
So here is the requirement, for any task id if duration is 0 then all it's predecessors should be linked to all the non-zero successors of the task ID in question.

So in the function here, I am looping through all the task ids and trying to link them with their all their non-zero successor, if found successor also has duration as zero then it will first look for successor of that id. It will run recursively until a non-zero successor is found.

I hope it helps.

Thanks

 

[StephenCrump]

It sounds like a tree structure, so your dictionary of dictionaries approach is certainly one way you could do this.

Can you post a simplified example so we can visualise your data. Perhaps you could also let us know how you intend referencing and using the results, e.g. do you need to understand the whole tree for any given taskID, or are you only interested in the successor taskID end-points?