I have procedure in Excel VBA which is need range parameter and also will produce output range exactly in active cell.

The code looks like this :

Sub test(oSource as Range)
  Dim oTarget As Range
  Dim oCell As Range

  Set oTarget = Range(ActiveCell, ActiveCell.Offset(0, 0))

  For Each oCell In oSource
    If oCell.Value <> "" Then

        oTarget.Offset(0, 0).Value = oCell.Text
        oTarget.Offset(0, 1).Value = oCell.Text

        Set oTarget = oTarget.Offset(1, 0)
    End If
End Sub

How do i call this ? I already try using function like this

Function(SourceRange as Range)
   Call test(SourceRange)
End Function

But it not working because test() will produce range in the active cell and will replace the function, excel will give circular value warning. I cant register it in macro, the create button always disabled when i click play. So how i call this Sub ?

  • You never use the SourceRange parameter that you pass to the sub in the first code. Do you understand how passing parameters work? – teylyn Apr 25 '16 at 6:52
  • You changed your code after my comment. Let us know when you're done editing your code sample, so we all know what we're looking at. – teylyn Apr 25 '16 at 6:54
  • @teylyn sorry, it's ok now. – Septian Primadewa Apr 25 '16 at 6:56
  • 1
    you can't change another cells' content from within a function, even if you call a sub from it. If you want to manipulate cell contents try to use worksheet events instead of calling subs from UDFs. If you need more help, then please explain a bit more you objective. – Máté Juhász Apr 25 '16 at 7:25

You call the first procedure by passing a range as the argument.


  • Can user change this without entering VBA ? Because some user who will use this is prefer run and choose source directly without entering VBA, they lack skill on it.. – Septian Primadewa Apr 25 '16 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.