0

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!!

braX
  • 11,506
  • 5
  • 20
  • 33
Matheus Assumpção Solinho
  • 67
  • 7
  • 2
    to select a range the sheet must be active. You may need to activate it first, but 99% of the time `.Select` is not needed and simply using correct references removes the need. See: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Scott Craner Dec 08 '22 at 23:15
  • Thanks man, im going to check this link. – Matheus Assumpção Solinho Dec 09 '22 at 14:34

1 Answers1

1

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
dcromley
  • 1,373
  • 1
  • 8
  • 23