Optional Range to Sub Excel VBA

Question

I've got an existing Macro I'd like to use by passing an rng argument, but if no rng is passed, the macro asks for a rng via inputbox, but I can't get the argument to work correctly.

Here is what I have:

Sub Four_Hundred_Fourty_Four_Split_Sub(Optional urng)
     ' Tried (Optional urng As Range), but it didn't work, couldn't run sub without arg

If IsEmpty(urng) = False Then 'urng = Missing
    Debug.Print urng
    Set urng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
End If

How can I get an optional rng argument to work in this method?

You are going to have to grab that input box as a string. Then set a Range variable using that string. Might want to test the validity of the string before you try that, however. — Wookies-Will-Code, Jul 14 '20 at 19:34

score 3 · Accepted Answer · answered Jul 14 '20 at 19:37

3

When you are using an optional argument in a procedure, you need to use IsMissing:

Sub Four_Hundred_Fourty_Four_Split_Sub(Optional urng)
    If IsMissing(urng) Then
        Set urng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
    End If
End Sub

Regards,

answered Jul 14 '20 at 19:37

Applecore

3,934
2
9
13

`urng` is passed as a `Variant` with this method. This may introduce bugs that are easily avoided by using `As Range` – ProfoundlyOblivious Jul 14 '20 at 19:45
1

@ProfoundlyOblivious `IsMissing` only makes sense in the context of `Variant`. It is always `False` for other data types. – GSerg Jul 14 '20 at 19:46
1

Note that if the user presses Cancel, `Application.InputBox` is documented to return `Variant/False`, and that cannot be used with `Set`. And if you remove `Set`, then when the user does select a range, it will be let-coerced to a Variant array instead. To work around this, see https://stackoverflow.com/a/36898101/11683. – GSerg Jul 14 '20 at 20:10

score 2 · Answer 2 · answered Jul 14 '20 at 19:40

2

Compare the range variable with nothing:

Sub test(Optional urng As Range)
    If urng Is Nothing Then
        Set urng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
    End If
    Debug.Print urng.Address
End Sub

works as expected.

answered Jul 14 '20 at 19:40

John Coleman

51,337
7
54
119

Then there's no way to distinguish between `test` and `test Nothing`. – GSerg Jul 14 '20 at 19:44
@GSerg Good point -- but it seems likely that asking the user for a range is the desired behavior if `Nothing` is explicitly passed. – John Coleman Jul 14 '20 at 19:47
When i use `Optional urng As Range` I can't run the macro without passing `urng` – FreeSoftwareServers Jul 14 '20 at 19:50
@FreeSoftwareServers Because you also need `= Nothing` in the end. – GSerg Jul 14 '20 at 19:53
I found that online and tried, still can't run macro without passing arg. Using `IsMissing` works perfectly. I feel stupid because I used debug and it told me that `urng = Missing` and I didn't think to test `IsMissing` lol. I did try `If urng = missing` but that didn't work. – FreeSoftwareServers Jul 14 '20 at 19:56
@GSerg I am able to use this exact code (without `=Nothing` at the end) and `test` by itself works as expected both in the Intermediate window and when called by another sub. – John Coleman Jul 14 '20 at 19:57
@FreeSoftwareServers Perhaps the problem is if you ran `test()` rather than `test`. In VBA, you don't put sub arguments in parentheses. Sometimes it is harmless, but sometimes it isn't when you actually do so. – John Coleman Jul 14 '20 at 20:02
@JohnColeman You are right; I don't know why the OP had this result then. Well, in any case both this answer and the accepted answer throw an exception if the user presses Cancel. Counter intuitively, `Set` should be removed - and then it will return the Variant array instead of the range, which I assume is the source of your original wrong test. – GSerg Jul 14 '20 at 20:02
[This](https://stackoverflow.com/a/36898101/11683) is required to overcome. – GSerg Jul 14 '20 at 20:08
I'm running Macro by pressing the start button inside the editor. – FreeSoftwareServers Jul 14 '20 at 20:14
@FreeSoftwareServers Then not even `= Nothing` will help. That mechanism does not support anything other than `Optional [As Variant]`. Otherwise this is perfectly callable from code. – GSerg Jul 14 '20 at 20:18
@GSerg I had no idea the editor was limiting, I always run my code by just clicking in a macro and running via start. It works that way now, but as you said, I have it undefined aka `Variant`. I will try calling from another macro w/o an argument! I do want to learn the best/proper methods and seems community agree's this is better. – FreeSoftwareServers Jul 14 '20 at 20:25
Can you post examples of calling this via code as I don't believe you are correct that this is due to me using the VBA editor to run the Macro, I think its perhaps how I am calling the test sub. – FreeSoftwareServers Jul 19 '20 at 03:02
You would call it (without any argument) by simply having `test` appear all by itself in another sub. One possibility is to have two subs. The first sub would always have a range as an argument. The second sub would have no arguments, but would use `inputbox` to get a range which it then forwards to the first sub, which does the actual work. This second sub would be the one that you would e.g. attach to a button or call from the developer tab. It is not uncommon to have one sub act as an IO wrapper of another sub. – John Coleman Jul 19 '20 at 17:58

Optional Range to Sub Excel VBA

2 Answers2