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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,515
Messages
5,511,740
Members
408,864
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top