Home > Blockchain >  Passing a Range as a optional parameter of a Sub routine
Passing a Range as a optional parameter of a Sub routine

Time:12-10

When a pass a range when calling a SubRoutine, the range get lost on the process.

I've got two modules:

  • Email
  • Final

On the Email Module i've got this sub:

Sub Send_Email(Type, MSG, operation, Optional RangeB As Range, Optional txtpath)

The parameter "Type" is either 1 or 2.

If i put 1 the sub will send an e-mail with a file.txt as an attachment. And if i put 2 the sub will put the range in the email body. That's why both RangeB and txtpath are optional parameters.

The attachment one works fine.

But when i put a range as parameter, RangeB cant be selected.

"RangeB.Select" -> ERROR (select method of range class failed)

I've tryed calling the sub by two different ways:

Call Send_Email(2, var_MSG, var_Op, Range("RangeBalko"))

Call Send_Email(2, var_MSG, var_Op, Templates.Range("RangeBalko"))

Templates -> ThisWorkbook.Sheets("Templates")

Someone knows how to resolve this issue ?

Best Regards!!

CodePudding user response:

Does this help? The single-cell range gets "MsgBox"ed and the multi-cell ranges get selected.

Sub test()
  Cells(1, 1) = "Hello"
  ActiveWorkbook.Names.Add "rng1", "=$C$3:$D$4"
  Call SendEmail(Range("a1"), Range("a1:b2"), Range("rng1"))
End Sub

Sub SendEmail(Optional rangeA As Range, Optional rangeB As Range, Optional rangeC As Range)
  MsgBox rangeA.Value
  rangeB.Select ' this can be done without "selecting": rangeB.delete
  Selection.Delete
  rangeC.Select ' if you must select
End Sub
  • Related