Macro to write custom function into cells

Hi

I've created a custom function call 'hours' that I've put into every cell in a large table. It uses two variables 'project' and 'plan_date'. See first part of this below:

Public Function Hours(project, plan_date As Date)

'Define the array of data to be used in function

Dim scheme_data As Variant
scheme_data = Sheets("TEST").Range("B2:P100")

'Define key dates of stages based on 'project'

Dim Gate_A2, Gate_B, Gate_C, ACL As Date

Gate_A2 = DateValue(Application.VLookup(project, scheme_data, 5, False))

My problem is that I don't want this to automatically recalculate, as it slows everything down, but I don't want to switch the whole workbook to manual calculation as others have to use it.

I thought I'd write a separate macro to enter the formula into the cells then paste special to just store the values in the table. The idea being that I'd call this macro every time I wanted to update the table.

Below is the code I tried just in one cell

    ActiveCell.FormulaR1C1 = "=IFERROR(Hours(RC2,R2C),0)"

When I run this, I get runtime error '13' type mismatch. The debugger takes me to the line in the function where Gate_A2 is defined. This is the first time that 'project' is referenced so I'm guessing that there's some issue with the order that excel is doing things and it isn't picking up that 'project' is defined by the function.

The formula works fine if I type it in manually.

Hope this makes sense, any ideas how I can get rid of the runtime error?

Thanks,

Rob

Neil, thanks for the replies. See whole function below:

Public Function Hours(project, plan_date As Date)

'Define the array of data to be used in function

Dim scheme_data As Variant
scheme_data = Sheets("TEST").Range("B2:P100")

'Define key dates of stages based on 'project'

Dim Gate_A2, Gate_B, Gate_C, ACL As Date

Gate_A2 = DateValue(Application.VLookup(project, scheme_data, 5, False))
Gate_B = DateValue(Application.VLookup(project, scheme_data, 6, False))
Gate_C = Application.VLookup(project, scheme_data, 9, False)
ACL = Application.VLookup(project, scheme_data, 11, False)

'Define what stage the project is in on 'plan_date'

Dim stage As Single

If plan_date < Gate_A2 Then
stage = 4.1
ElseIf plan_date < Gate_B Then
stage = 4.2
ElseIf plan_date < Gate_C Then
stage = 4.3
ElseIf plan_date < ACL Then
stage = 4.4
Else: stage = 4.5
End If

'Define the length in days of the curernt stage from dates in scheme_data

Dim stage_length As Integer
If stage = 4.2 Then
stage_length = Application.VLookup(project, scheme_data, 13, False)
ElseIf stage = 4.3 Then
stage_length = Application.VLookup(project, scheme_data, 14, False)
ElseIf stage = 4.4 Then
stage_length = Application.VLookup(project, scheme_data, 15, False)
Else: stage_length = 2
End If

'Check scheme_data to see if Engineer is the Lead, complexity of project and scope of in house assurance

Dim lead As String
Dim complex As String
Dim scope As String

lead = Application.VLookup(project, scheme_data, 2, False)
complex = Application.VLookup(project, scheme_data, 4, False)
scope = Application.VLookup(project, scheme_data, 3, False)

'Number of hours defined as total hours for current stage of project, divided by stage_lengh, converted from days to weeks
'Check if current stage is being covered in house, if not, return hours = 0

If stage = 4.2 Or stage = 4.3 Or (stage = 4.4 And scope = "whole scheme") Then
Hours = 7 * Application.VLookup(lead & complex & stage, Sheets("TEST").Range("W2:X19"), 2, False) / stage_length
Else: Hours = 0
End If
End Function

Thanks,

Rob

Neil,

Just playing around with this again. The problem seems to be when the IFERROR is true. Works ok (if probably a little inefficiently) when the hours function doesn't return an error

Ok think I might have sorted this out.

I've added "On Error Resume Next" on the first line of the function, it seems to run ok now.