0

I have a scheduling excel macro that lists everyday of the month as a separate excel worksheet. In my macro the "Sunday" sheets total the hourly hours worked per employee and give me the weekly total. I tried to create a function that will allow me to select which sheets I need as far as an upper and lower range but I can't get it to work. What is the issue? Currently I have:

Function SampleSum(ByVal ZZ As Integer, ByVal XX As Integer) As Integer
Application.Volatile
SampleSum = Sheets(ZZ).Range("BA5") + Sheets(XX).Range("BA5")
Sheets(XX).Range("BB5") = SampleSum
End Function

I would like to be able to "call" the function with any 2 number or variables taking the place of ZZ & XX.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Stove
  • 21
  • 6
  • Function that you described is working... – Nicolai Jul 11 '14 at 13:52
  • 1
    A function called from the worksheet can **only** return a value to the calling cell. You cannot use a UDF to manipulate other range/cells. – David Zemens Jul 11 '14 at 14:05
  • 1
    See [this post](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) for explanation and possible workaround. otherwise, you have to do this as a Subroutine. – David Zemens Jul 11 '14 at 14:07

1 Answers1

1

First of all since you declared your application "volatile", I wouldn't programmatically edit your worksheet from there, since it COULD go in a loop: http://msdn.microsoft.com/en-us/library/office/ff195441%28v=office.15%29.aspx Second, a function RETURNS something. A Subroutine doesn't. If you have only to write in a cell I would recommend to use a Subroutine.

Function SampleSum(ByVal ZZ As Integer, ByVal XX As Integer) As Integer
    Application.Volatile
    SampleSum = Sheets(ZZ).Range("BA5") + Sheets(XX).Range("BA5")
End Function

Sub Main()
    Sheets(XX).Range("BB5") = SampleSum(ZZ, XX)
End Sub

ZZ and XX in the "Main" Sub can be numbers of your choice

Noldor130884
  • 974
  • 2
  • 16
  • 40
  • My apologizes, that was just a sample function I was trying to find a work around with. In the original sub the code was sum = 0 'For a = 5 To 97 'sum = 0 'For g = (c - 7) To c 'sum = sum + Sheets(g).Cells(a, 53) 'wbMain.Worksheets(c).Cells(a, 54) = sum 'Next g 'Next a . I need as a function that will take 2 sheet indexes of my choosing, instead of having this code repeat. I can't get it to work as a function. And the sample function above doesn't update the weekly total if I update any of the daily totals after the macro was ran. I thought Application.Volatile would fix that – Stove Jul 11 '14 at 13:57
  • Have you tried using the "On worksheet change" event? – Noldor130884 Jul 11 '14 at 14:24
  • These are the basics: http://msdn.microsoft.com/en-us/library/office/ff839775%28v=office.15%29.aspx You should go in the module of your selected worksheet and then set those 2 dropdown menues to "Worksheet" and "Change". Then you can call your function. This will occur every time a change in the worksheet has been made. BEWARE: you could be trapped in loops with this! – Noldor130884 Jul 11 '14 at 14:27