Using worksheet name as function input

xyz2006tor

New Member
Joined
Aug 3, 2010
Messages
3
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!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
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

New Member
Joined
Aug 3, 2010
Messages
3
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Code:
function SUMCOL(byval WksName as string) as double
SUMCOL = application.sumif(worksheets(WksName).columns(1),">0")
end function
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,277
Messages
5,657,801
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top