User Defined Function 1

[PWD]

Hello. I can't really get my head around the syntax for these UDF's. I'm no stranger to VBA but this is all new to me. The code below is just part of what I need to get around the 7 'IF' limit in Excel 2000. We have many sheets in a workbook that has cells for depreciation based on the particular month. We have a range of dates on Sheet 'D' - A1 = April to A12 = March the following year. We need cells to refer to this table and depending on what the correspond to, pick values from another range of data.

Function TestDate()
Select Case Range("Q7")
Case Sheets("D").Range("A1")
ActiveCell = "XXX" 'Month 1 - HAS NO CUMULATIVE DEPRECIATION
Case Sheets("D").Range("A2")
ActiveCell = Range("AK10")
Case Sheets("D").Range("A3")
ActiveCell = Range("AL10").........etc
End Select
End Function

Can someone please point out my schoolboy error?
Many thanks, Des.

 

[DaveInIowa]

Some things to note:
1) You should define what type is being returned from your function (even though it's not required).
2) You need to work with range Values, not the range itself.
3) You never set the function's return value.

Function TestDate() [red]As Variant[/red]
    
    Select Case Range("Q7")[red].Value[/red]
        Case Sheets("D").Range("A1")[red].Value[/red]
            [red]TestDate[/red] = "XXX" 'Month 1 - HAS NO CUMULATIVE DEPRECIATION
        Case Sheets("D").Range("A2")[red].Value[/red]
            [red]TestDate[/red] = Range("AK10")[red].Value[/red]
        Case Sheets("D").Range("A3")[red].Value[/red]
            [red]TestDate[/red] = Range("AL10")[red].Value[/red]
    End Select
    
End Function

 

[PWD]

Thanks Dave. I've duly made those changes but still being 'Mr Thicky' I'm not even sure what to put in the cell to activate this function - sorry!

Des.

 

[HarleyQuinn]

You just need to write:

=TestDate

into the cell you want the formula in.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PWD]

Yeah, well that's what I thought but I just get the old #NAME? instead.

Des.

 

[DaveInIowa]

Maybe you need to qualify your "Q7" range and/or provide a catch-all clause in your Select statement.

    Select Case [red]Sheets("D").[/red]Range("Q7").Value
        Case Sheets("D").Range("A1").Value
            TestDate = "XXX" 'Month 1 - HAS NO CUMULATIVE DEPRECIATION
        Case Sheets("D").Range("A2").Value
            TestDate = Range("AK10").Value
        Case Sheets("D").Range("A3").Value
            TestDate = Range("AL10").Value
        [red]Case Else: TestDate = "Error"[/red]
    End Select

 

[PWD]

Thanks Dave, I'll check that out. It's just after 8 a.m. so I've the whole of the day to get this figured!
Des.

 

[PWD]

Halleluiah!! I was able to step through (F8) your version and prove that it was working as a Function and picking up the correct answers. In desperation I decided to type =TestingDate() in the cell. Well whadya know? It worked. I copied it and amended it back (just to see) as below.......

Function TestingDate() As Variant
    
    Select Case Range("Q7") 'This Sheet - where I want the Function result
        Case Sheets("D").Range("A1") 'The sheet that holds the dates
            TestingDate = "XXX" 'Month 1 - HAS NO CUMULATIVE DEPRECIATION
        Case Sheets("D").Range("A2")
            TestingDate = ActiveSheet.Range("AK10")
        Case Sheets("D").Range("A3")
            TestingDate = ActiveSheet.Range("AL10")
        Case Else: TestingDate = "Error"
    End Select
    
End Function

....... and it still works! Result. Thanks again.

Des