Excel Custom Function [Solved/Closed]

Report
-
 AG -
Hello,

i have created a custom excel function which works fine. the only problem being, you need to press F2 on the custom formula cell and give enter to refresh the formula.

is there any way to refresh the formula every time the workbook is opened or before closing or while moving on to another row in the workbook.

thanks,
moses

14 replies

Maybe you tried this before, but this may be the cause:

Go to Tools
Options
Calculation
Make sure it is in AUTOMATIC and not MANUAL
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5755 users have said thank you to us this month

Hi,

I had the same problem once with my MS excel worksheet before.. When I input a formula in a cell and copied it to other cells, it copies the formula but displays the one from the originating cell. I had to press F2 then hit enter to "refresh" the cell. It would now then display the correct figure for the copied formula.

I didn't get to fix this problem with the said worksheet though. I just opened a new worksheet and all worked fine. I guess there was just some sort of bug with the file itself. Hope this helps.
Use text to Column option in excel
That works perfect. Thanks!
Had the same problem, found this answer and works an abolute treat. Saved a lot of work - thank you!!!
Posts
18
Registration date
Thursday May 15, 2008
Status
Member
Last seen
May 29, 2009
3
hello moses,

well i think this isnt unforfunately possible to be done in excel if you were using infopath then it could have been possible
hi...thanks for the try...maybe if u have a look at the code u can get a better picture
all is want is the formula to refresh itslef when u open or close workbook.


Function dis_multiple(end_date As Variant, msr_type As String, scheme As String, category As String, dob As Variant) As Variant
Dim title_row As Long
Dim age As Long
'Dim end_date As Date

Application.Calculation = xlCalculationAutomatic


Row = ActiveCell.Row
title_row = 5
age = Year(Now()) - Year(dob)

With ActiveSheet
msr_type = .Cells(Row, GetCol(title_row, "MSR Type"))
scheme = .Cells(Row, GetCol(title_row, "Scheme"))
category = .Cells(Row, GetCol(title_row, "Leaver Category"))
dob = .Cells(Row, GetCol(title_row, "DOB"))
end_date = .Cells(Row, GetCol(title_row, "Last Day Of Paid Service / Employment End Date"))
End With


end_month = Month(end_date)

'If msr_type = "" Or scheme = "" Or dob > 1900 Then

If msr_type = "New Joiners" And dob > 1900 Then
If scheme = "SORAS/DIS" Then
If age < 58 Then
dis_multiple = 4
Else
dis_multiple = 1
End If

Else
End If

Else
'MsgBox "Please see that the MSR Type and Date of Birth fields are filled in for New Joiners"
'Exit Function
End If

If msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "SORAS/DIS" Then
dis_multiple = 4
Else
End If
Else
End If



If msr_type = "Leavers" And scheme = "SORAS/DIS" Then
If category = "Regular" Then '
If end_month = Month(Now()) Then
dis_multiple = 4
Else
dis_multiple = 1
End If
ElseIf category = "Absconder" Then
dis_multiple = 4
ElseIf category = "Voluntary" Then
dis_multiple = 1
Else
MsgBox "Leaver category is blank"
End If
Else
End If


If msr_type = "New Joiners" Or msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "DIS/MS" Then
dis_multiple = 1
Else
End If
Else
End If

If msr_type = "Leavers" And scheme = "DIS/MS" Then
dis_multiple = 1
Else
End If

If msr_type = "New Joiners" Or msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "MS" Then
dis_multiple = 1
Else
End If
Else
End If

If msr_type = "" And scheme = "" And category = "" And dob = "" And end_date = "" Then
dis_multiple = ""
Else
End If


If scheme = "MS" Then
dis_multiple = ""
Else
End If


Application.Calculation = xlCalculationAutomati
End Function
> moses
Have you tried "Application.Volatile".
> just-passing-by
Application.Volatile True
Add this to the beginning of the code, it will make excel recalculate the function every time anything is changed in the workbook (Also beore saving and after opening, if all goes well...). "True" is needed too I guess.
try
Application.CalculateFull
Hello,
Have you tried the following:

Private Sub Workbook_Open()
Application.CalculateBeforeSave = True
End Sub
'the following can be resources consuming if you have a lot of formulas to calculate on the sheet
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Calculate
End Sub
Edit the code so that F2 is not in the code, as well as that line and anything that links to that line.