Thursday, March 19, 2015

How to run Sub with parameter in Excel VBA?

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.Activate

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

Set oTarget = oTarget.Offset(1, 0)
End If
Next
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 ?

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...