Excel Custom Function

Solved/Closed
moses - Sep 15, 2008 at 01:34 AM
 AG - Oct 7, 2010 at 08:52 PM
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

6 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
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.
1
Use text to Column option in excel
1
That works perfect. Thanks!
0
perfect!!!!
0
Had the same problem, found this answer and works an abolute treat. Saved a lot of work - thank you!!!
0
kallor234 Posts 18 Registration date Thursday May 15, 2008 Status Member Last seen May 29, 2009 3
Sep 15, 2008 at 05:34 AM
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
0
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
0
just-passing-by > moses
Nov 28, 2008 at 04:30 PM
Have you tried "Application.Volatile".
0
1 thing to add > just-passing-by
Sep 18, 2009 at 05:36 AM
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.
0
try
Application.CalculateFull
0

Didn't find the answer you are looking for?

Ask a question
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
0
Edit the code so that F2 is not in the code, as well as that line and anything that links to that line.
0