1

I have this function within my code:

Function get_header(ByVal rw As Range) As Scripting.Dictionary
    Dim header    As New Scripting.Dictionary
    Dim used      As Range

    Set used = Range(rw.Cells(1, 1), rw.Cells(1, rw.Cells(1, rw.Columns.Count).End(xlToLeft).Column))

    For Each cl In used.Cells
        header.Add cl.Value, cl.Column
    Next
    
    Set get_header = header
End Function

What the function does is it takes the table header and creates a dictionary of column names and the respective indexes, so that the order of columns is not important for the rest of the code.

My question is: is it necessary to use a separate variable to store the value throughout the loop, or can I

  1. edit the returned value ("get_header") whole time instead of only passing the value at the end or
  2. use the With structure like this:
Function get_header(ByVal rw As Range) As Scripting.Dictionary
    Dim used      As Range

    With rw
        Set used = Range(.Cells(1, 1), .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
    End With

    With get_header
        For Each cl In used.Cells
            .Add cl.Value, cl.Column
        Next
    End With

End Function

Also, why should I use any of these structures instead of the others? Thanks for any advice, guys.

Improve this question
3
  • 1
    As much as this is ultimately opinion based, I vote in favor of option #1. #2 kind of hides the return value for me, while #1 is explicit as it can be. Commented Aug 24, 2020 at 14:58
  • In addition to being opinion-based, your question is confusing in the sense that both your #1 and #2 are about using the function name as a variable in the body of the function and thus don't really illustrate the contrast that you seem to be trying to make. Commented Aug 24, 2020 at 15:44
  • 1
    FYI this line Set used = Range(rw.Cells(1, 1), ... in a regular code module will error if the sheet with rw is not active. Something like Set used = rw.Parent.Range(rw.Cells(1, 1), ... would be more robust. Commented Aug 24, 2020 at 16:14

2 Answers 2

Reset to default
4

Wanted to comment this, but too long for a comment:

When I have a function that "calculates" the return value step by step, I usually prefer to have an intermediate variable for the following reasons (note that using extra variables comes at no cost during runtime, that's not an issue):

  • Naming: I don't like assigning values to something that is called getSomething.
  • when used right-sided, it avoids ambiguity between the "intermediate" value and a recursive call to the function: getSomething = getSomething + getSomething(p). The compiler can handle that, but my brain? Not so much. I find retVal = retVal + getSomething(p) is much clearer.
  • When running on an error condition in the middle of execution, I can easily do an Exit Function without thinking about what was already calculated.

But at the end of the day (and this valid also for the question if of if not to use a With-statement), there are two things that matters: (1) Which code is easier to read and understand. and (2) Which code is less likely to hold an error. As human brains don't work all the same: Find you personal style and stick to it.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I also prefer this approach. For one thing, most programming languages have return so that return calculated_value is the only option. Using the idiom of function_name = calculated_value makes it clear just what the function returns whereas if you have several lines like function_name = possibly_intermediate_value, it might not be clear exactly which line functions as a return.
1

I would favor your first example instead of editing the function's return value more than once in a single function call.

Depending on whether error trapping breaks or is silent (i.e. on error resume next), you may get different and unintended results. Importantly, you may get unintended results and not know it.

If a few cycles run fine, and the function result variable is updated a few times, then some error occurs with processing a later cycle (due to inputs, scope, range, etc issues for the later part of the input array, or even just memory or focus or other runtime issues), you will have a problem: the function returns a premature (therefore incorrect) result, but appears as though it has run properly.

I'd recommend coding to avoid this situation, whether or not errors are suppressed. An easy way to do this is to not set the function result object more than once per possible function execution path.

Your first code block seems to conform to this thinking already. Each of your alternate suggestions 1 and 2 do not.

Improve this answer

2 Comments

"I'd recommend coding to avoid this situation" ... seems like a reason to avoid On Error Resume Next (one of many) rather than a reason to not use the function name as a variable.
@JohnColeman That's assuming the OP has control over error handling in the whole procedure space. I agree it's often best not to use On Error Resume Next for many reasons. However, even if errors are not silent, using the function name as a variable might require more in-depth anticipatory error trapping within the function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.