Macro Jump to other Public Functions which are not called

gmalpani

New Member
Joined
Dec 24, 2011
Messages
33
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
#
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
I have that same issue, but toggling Application.Calculation addresses it. (But its still a pain.)
 

gmalpani

New Member
Joined
Dec 24, 2011
Messages
33
Any other way of setting calculation to Automatic and not letting macro jump to Public Functions?
Please help.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
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.
 
Solution

gmalpani

New Member
Joined
Dec 24, 2011
Messages
33
Hi Experts,

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

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top