MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Can you pass a worksheet as an argument to a function

  Asked By: Decha    Date: Sep 22    Category: MS Office    Views: 5449

I am not very conversant with the VBA's object oriented progaming
rules. I was wondering if I can pass a worksheet as an argument to a
My function is below. I am passing B51 and a worksheet named "Trader
Template" as the two arguments. Will VBA create separate instances
of this worksheet when this function is used across many rows?

Function GetIRR(coup As Range, whsht As Worksheet) As Double
Dim rowF As Integer
rowF = coup.Row

Dim shtT As Worksheet, shtF As Worksheet
Set shtT = wksht
Set shtF = Sheets("Trader")

shtT.Cells(6, 1).Value = shtF.Cells(rowF, 1).Value
shtT.Cells(6, 2).Value = shtF.Cells(rowF, 2).Value
shtT.Cells(6, 3).Value = shtF.Cells(rowF, 3).Value
shtT.Cells(6, 4).Value = shtF.Cells(rowF, 4).Value
shtT.Cells(6, 5).Value = shtF.Cells(rowF, 8).Value
shtT.Cells(6, 6).Value = shtF.Cells(rowF, 9).Value
shtT.Cells(6, 7).Value = shtF.Cells(rowF, 10).Value

shtT.Cells(9, 10).Value = shtF.Cells(rowF, 2).Value - shtF.Cells
(rowF, 10).Value

GetIRR = shtT.Cells(6, 11).Value

End Function



1 Answer Found

Answer #1    Answered By: Hattie Howard     Answered On: Sep 22

Unless you explicitly create  a new worksheet, both whsht and shtT are just
pointers to the same worksheet. Any changes you make are made on the real

Didn't find what you were looking for? Find more on Can you pass a worksheet as an argument to a function Or get search suggestion and latest updates.