-1

Not entirely sure how to ask this:

I have a named range that I am using in a sub and will be using the same named range in a function.

Should I pass the named range as an argument to the function or just redefine the named range in the function?

I don't need to manipulate the named range immediately in the function - just need to use the named range in the function (see code snippet)

Vague question so I'll try to define some criteria with which to judge which is "better":

  • Code readability
  • Execution speed
  • Security of code? (e.g. if you don't want people to be able to "see" your hidden code)
  • Other criteria?

Option 1: Pass range as argument

Dim rRng as Range
Set rRng = Sheet3.Range ("aNamedRange")

Call TestFunction (rRng)

'Here's what the function "TestFunction" would look like
Function TestFunction (rRange as Range)

rRange (1,1) = "blah"
End Function

Option 2: Redefine range in function

Dim rRng as Range
Set rRng = Sheet3.Range ("aNamedRange")

Call TestFunction

'Here's what the function "TestFunction" would look like
Function TestFunction ()

Dim rRange as Range
Set rRange = Sheet3.Range ("aNamedRange")

rRange (1,1) = "blah"
End Function
newtovba
  • 33
  • 6
  • Ah yes - I'll edit the code. Just quickly retyped some pseudocode for explanation purposes – newtovba Sep 19 '19 at 01:44
  • In this scenario, I will use option 2 but delete 1st 2 lines before `Call TestFunction` – Siddharth Rout Sep 19 '19 at 01:56
  • 2
    ...on the other hand, let's say you have a procedure that creates a sheet and automates certain things in the page header & footer: you're spawning an object (`wb.Worksheets.Add`) and capturing it into a local variable - `Set sheet = wb.Worksheets.Add`, ...where `wb` might as well be a parameter. Or perhaps you're writing some lookup or average function, and you need local variables to enhance the readability and overall abstraction level of your code. Bottom line might be, use locals for your procedure's implementation details, parameters for your dependencies. Locals good, globals evil =) – Mathieu Guindon Sep 19 '19 at 02:21
  • As for the "security" bullet... [nope](https://stackoverflow.com/a/27508116/1188513). Write code for it to be *read* - code runs, but you're not writing for the computer; you're writing code for yourself to be able to read, understand, maintain, and extend - there *are* good (commercial) obfuscators out there for VBA, but that's a concern for the distribution of your code, and it should have zero interference with how you're going to write it, and how *future you* (or your successor?) is going to work with that code. Perf bullet isn't relevant at that level; the bottleneck won't be there. – Mathieu Guindon Sep 19 '19 at 02:34
  • 1
    @mathieuguindon: you may want to put that as an answer? – Siddharth Rout Sep 19 '19 at 02:35

1 Answers1

3

As with many, many things in programming, the answer is a rather disappointing "it depends".

You want a Function procedure to take its dependencies in as (ByVal) parameters, and then use them to compute and return a result to the caller. And if a function does that without any side-effects on global state, you get to call it a pure function! In a Sub procedure, you will also take your dependencies as parameters as much as possible. No need for a Call keyword though: 

foo = GetFoo(42)
MsgBox "hello"

...on the other hand, let's say you have a procedure that creates a sheet and automates certain things in the page header & footer: you're spawning an object (wb.Worksheets.Add) and capturing it into a local variable - Set sheet = wb.Worksheets.Add, ...where wb might as well be a parameter. Or perhaps you're writing some lookup or average function, and you need local variables to enhance the readability and overall abstraction level of your code. Bottom line might be, use locals for your procedure's implementation details, parameters for your dependencies. Locals good, globals evil =)

As for the "security" bullet... nope. Write code for it to be read - code runs, but you're not writing for the computer; you're writing code for yourself to be able to read, understand, maintain, and extend - there are good (commercial) obfuscators out there for VBA, but that's a concern for the distribution of your code, and it should have zero interference with how you're going to write it, and how future you (or your successor?) is going to work with that code.

Performance isn't a concern at that level; if there's a bottleneck, it very likely won't be because you're passing an argument.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235