Macro Jump to other Public Functions which are not called

[gmalpani]

Hello Experts

Here is the situation. I have an excel file which already have bunch of macros including some Public Functions which are real Pain.
I am using this excel to build a tool which gives output in an automated way.
To do so, I added few worksheets to get user inputs and calculation sheet.

Below three modules (Module 1 to 3) are one which I wrote. Module 4 was existing which is a Public Function.
Whenever I run my Module 1 (main module) it run fine till last step - Unspeed. When It executes and call Unspeed Module (Module 3), and when it reach to Automatic calculation step #Application.Calculation = xlCalculationAutomatic# it jump to Module 4 Public Function which is not even called.
Here I have not posted full Module 4 but it is quite big and also run repeatedly for bunch of worksheets. So, it takes long time atleast 5- 6 minutes to run my macro.
I want restrict my module to go this Public Function.
I tried deleting this Public Fucntion Module but then My macro jumps to some other Public Function in other module.

Another way when I try not to Speed in begining then at evry next steps of simple copying values it jumps to Public Function.

Please help to get rid of this. As I am not a expert in macro, I really look forward to experts help here.


Module 1: To generate Pricing
#
Sub PricingReference()
'Get Base Reference IRP for X Mllion Subs for all products

Speed
'All Products set to be Included in the offer
Sheets("Calculation for All Options").Range("C4") = "Included"
Sheets("Calculation for All Options").Range("C9") = "Included"
Sheets("Calculation for All Options").Range("C10") = "Included"
Sheets("Calculation for All Options").Range("C12") = "Included"
Unspeed

End Sub
#

Module 2: Speed
#
Public Sub Speed()
'switch off unnecessary functions - speed up runtime of macros

On Error Resume Next
If Not mbInSpeed Then
Application.ScreenUpdating = False 'disable sheet screen updating
Application.DisplayAlerts = False 'ignore alerts
Application.EnableEvents = False 'ignore events
ActiveSheet.DisplayPageBreaks = False
mlCalcStatus = Application.Calculation
Application.Calculation = xlCalculationManual 'halt sheet calculations
mbInSpeed = True
Else
'we are already in speed - Don't do anything
End If
End Sub
#

Module 3: Unspeed
#
Public Sub Unspeed()
'switch on all functions at the end of any macro

On Error Resume Next
Application.ScreenUpdating = True 'enable sheet screen updating
Application.DisplayAlerts = True 'alerts
Application.EnableEvents = True 'events
'ActiveSheet.DisplayPageBreaks = True 'update page breaks
If mbInSpeed Then
Application.Calculation = mlCalcStatus
Else
'this shouldn't be happening, but anyway, put calc to auto
Application.Calculation = xlCalculationAutomatic 'automatic sheet calculations
End If
mbInSpeed = False
End Sub
#

Module 4: Public Functions (pain point)
#
Function f_MapClassificationXLS(value As Variant, Optional value2 As String, Optional xlMaterialType As Integer) As Integer
' value = classifications like HW, RTU TL, ASW, etc.
' value2 = sales item description
' xlMaterialType = return values as pricing category (e.g. "Hardware") or classification (e.g. "HW") as numeric value

Dim v1 As Variant 'cell value, if Level2 summary in legacy view
Dim v2 As Variant 'cell value, if Level2 summary in Gen2 view
Dim vp As Variant 'cell value, if Pricing Category is returend

Application.Volatile True
'''
'''
End Sub
#

 

[mikerickson]

I have that same issue, but toggling Application.Calculation addresses it. (But its still a pain.)

 

[gmalpani]

Any other way of setting calculation to Automatic and not letting macro jump to Public Functions?
Please help.

 

[mikerickson]

I run into this most when using UDFs on a worksheet.
I either re-code the UDFs as a Named formula

Oddly enough, if I use Named Formulas, e.g. Name: myFormula RefersTo: =MyUDF(A1) the issue is less than if I put =MyUDF(A1) directly into cells.

Or I put Application.Calculation= xlCalculationManual (and later, xlCalculationAutomatic) in the macro that triggers the issue.

 

[gmalpani]

Hi Experts,

Thanks a lot for your comments.
Though this cannot be resolved yet. But I am using the way it is.