Using worksheet name as function input

[xyz2006tor]

Hi all--

I'm new to this great community, and I'm so excited to be part of it, especially since I've just started learning VBA!

I have one question: How can I use a worksheet name as a function input? The function I'm working on goes to a sheet and sums all the (positive numerical) entries in column 1 until it reaches an empty cell; I hope to be able to specify the sheet when calling this function.

Thanks so much for the great information on here!

 

[Blade Hunter]

Can you give an example? The VBA code is activesheet.name, this will return the name of the sheet but how you use that depends on the context of what you need it for.

 

[xyz2006tor]

I'd like to perform an operation on a worksheet (e.g. SUMCOL: add up all the positive values in column 1). So, I'd like SUMCOL to be of the form SUMCOL(Worksheet name).

Does that make sense?

 

[Fazza]

function SUMCOL(byval WksName as string) as double
SUMCOL = application.sumif(worksheets(WksName).columns(1),">0")
end function

 

[xyz2006tor]

That code is gorgeous. Thanks so much!

 

[Fazza]

You're welcome. That code shows the idea. In practice, the type might be variant (not double) to be able to return errors. And there should be some error handling such as check the sheet exists. And that the routine is not used recurvisely, such as call for the sum of "Sheet1" when the formula itself is located in column A of "Sheet1" - that would cause it to loop on itself. cheers