CODEWITHSUNDEEP
excelvba
Avery Torrance
Avery Torrance

Reputation: 1

How to make a function use the data that is on the sheet, the function is written on?

Ill use an example to explain my problem. Lets say I write the function, 

Function Sums()

Sums = Cells(1, 1) + Cells(1, 2)

End Function

Then, lets say I have two spreadsheets, A and B. If I refresh all spreadsheets while on A, the Sums() function on spreadsheet B, utilizes the data from spreadsheet A. How can I make the functions utilize the data specifically on the spreadsheet the function is written on?

Upvotes: 0

Views: 59

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57693

The issue here is when using Cells(1, 1) or Range("A1") without specifying in which worksheet that is, Excel starts guessing which worksheet you mean. The result of that guessing may be different.

  • Code in Module:
    when the code is in a module it assumes that you mean the ActiveSheet (the sheet which is on top at the moment the code runs)

  • Code in Sheet:
    when the code is in a worksheet then it assumes that you mean that worksheet the code is in.

So we see any Cells(1, 1) or Range("A1") without specifying a worksheet is a bit lottery.

The next issue here is that the Sums() function can not know on which sheet it was written on unless you give the cells as parameters in a range:

Public Function Sums(Rng As Range) As Double 'specify a return type here
    Sums = Application.WorksheetFunction.Sum(Rng)
End Function

So you can use it in a cell like: =Sums(A1:B1)

Another advantage is that you don't need to press re-calculate, because it watches if the values in the given range Rng changed and automatically recalculates then.

Note: A function should always use the parameters given to the function instead of accessing the worksheet directly. Otherwise the function might show wrong values (unless you make it volatile).

Upvotes: 1

dv3
dv3

Reputation: 4579

I'm not 100% sure what you're asking. But here is a guess: Be sure to always (fully) define your worksheets. 

Function Sums()
  Dim wb as Workbook
  Dim ws as Worksheet
  Dim ws2 as Worksheet
  Dim sum as Integer
  Set wb = Application.ActiveWorkbook
  Set ws = wb.Worksheets("Sheet1")
  Set ws2 = wb.Worksheets("Sheet2")

  'now you defined your worksheets and always know which cells you're referring to 
  sum = ws.Cells(1, 1) + ws2.Cells(1, 2)
End Function

Also I generally recommend to use ranges, instead of Cells(x,y). Let me know if this answers the question

Upvotes: 0

Related Questions