Accessing Custom functions

[ben100]

Hi,

I made a custom function the other day using visual basic and it worked as it should and gave me the proper return.

The fuction was this - as you can see it takes 4 arguments

Function HOME(HGMEAN, HCMEAN, AGMEAN, ACMEAN)

HG0 = Application.Poisson(0, HGMEAN, False)
HG1 = Application.Poisson(1, HGMEAN, False)
HG2 = Application.Poisson(2, HGMEAN, False)
HG3 = Application.Poisson(3, HGMEAN, False)
HG4 = Application.Poisson(4, HGMEAN, False)
HG5 = Application.Poisson(5, HGMEAN, False)
HC0 = Application.Poisson(0, HCMEAN, False)
HC1 = Application.Poisson(1, HCMEAN, False)
HC2 = Application.Poisson(2, HCMEAN, False)
HC3 = Application.Poisson(3, HCMEAN, False)
HC4 = Application.Poisson(4, HCMEAN, False)
HC5 = Application.Poisson(5, HCMEAN, False)
AG0 = Application.Poisson(0, AGMEAN, False)
AG1 = Application.Poisson(1, AGMEAN, False)
AG2 = Application.Poisson(2, AGMEAN, False)
AG3 = Application.Poisson(3, AGMEAN, False)
AG4 = Application.Poisson(4, AGMEAN, False)
AG5 = Application.Poisson(5, AGMEAN, False)
AC0 = Application.Poisson(0, ACMEAN, False)
AC1 = Application.Poisson(1, ACMEAN, False)
AC2 = Application.Poisson(2, ACMEAN, False)
AC3 = Application.Poisson(3, ACMEAN, False)
AC4 = Application.Poisson(4, ACMEAN, False)
AC5 = Application.Poisson(5, ACMEAN, False)

PROBH0 = (HG0 + AC0) / 2
PROBH1 = (HG1 + AC1) / 2
PROBH2 = (HG2 + AC2) / 2
PROBH3 = (HG3 + AC3) / 2
PROBH4 = (HG4 + AC4) / 2
PROBH5 = (HG5 + AC5) / 2
PROBA0 = (HC0 + AG0) / 2
PROBA1 = (HC1 + AG1) / 2
PROBA2 = (HC2 + AG2) / 2
PROBA3 = (HC3 + AG3) / 2
PROBA4 = (HC4 + AG4) / 2
PROBA5 = (HC5 + AG5) / 2

LIKELIHOOD = (PROBH1 * PROBA0) + (PROBH2 * PROBA0) + (PROBH3 * PROBA0) + (PROBH4 * PROBA0) + (PROBH5 * PROBA0) + (PROBH2 * PROBA1) + (PROBH3 * PROBA1) + (PROBH4 * PROBA1) + (PROBH5 * PROBA1) + (PROBH3 * PROBA2) + (PROBH4 * PROBA2) + (PROBH5 * PROBA2) + (PROBH4 * PROBA3) + (PROBH5 * PROBA3) + (PROBH5 * PROBA4)

HOME = LIKELIHOOD * 100
HOME = Application.Round(HOME, 2)

End Function


Okay, so i came back to today and select the function in excel but it does not work. Excel says the function has no arguments and the cell has a #NAME error (and this is a recurring problem as I've had this happen with other custom functions to).

Is there something I need to do in order to access the function again?
In the workbook the function appears as =Football2.xlsm!Module4.HOME() rather than simply HOME - is this the problem?

Thanks for any help,

ben.

 

[Firefly2012]

Hi Ben

Does the function code actually reside in Module 4 of the workbook that contains the =HOME() formulas?

 

[p45cal]

The function might be more universally accessible if you put it into the Personal.xls (or equivalent in Excel 2007-2010).

BTW, your function can be shortened:

Function HOME(HGMEAN, HCMEAN, AGMEAN, ACMEAN)
Dim PROBH(0 To 5), PROBA(0 To 5)

For i = 0 To 5
  PROBH(i) = (Application.Poisson(i, HGMEAN, False) + Application.Poisson(i, ACMEAN, False)) / 2
  PROBA(i) = (Application.Poisson(i, HCMEAN, False) + Application.Poisson(i, AGMEAN, False)) / 2
Next i

For i = 0 To 4
  For j = i + 1 To 5
    LIKELIHOOD = LIKELIHOOD + PROBH(j) * PROBA(i)
  Next j
Next i

HOME = Application.Round(LIKELIHOOD * 100, 2)
End Function

 

[ben100]

Thanks for both replies.

Shortened version is much nicer

Macro's were being disabled everytime I re-started excel. I don't know why or how, but it's all sorted.

No problem anymore. Thanks again.