Hello all gurus

I have a project containing two xlsm files: Accounting.xlsm and Common.xlsm..
In Accounting I use a VBA procedure to copy row-by-row from one worksheet to another. I copy dates, numbers an strings.

In the other file, Common, there is a function checking if a date is within a range and this function is used and called only in Common.xlsm.

If both workbooks are open when I run the copying routine in Accounting, the date checking function in Common is called each time the row data is pasted. In the call stack a "non basic code" is calling that function.

If only Accounting workbook is open, the copy-paste routine runs correctly and fast.

How can I trace, where is the call to the function?

Håkan

If I correctly understand, you have the same name procedure defs in
both files. To avoid issues like this I declare each
mdule/userform/class that reuses same defs as 'Private', meaning the
code page starts with...

Option Explicit
Option Private Module

Const sModule$ = "ModuleNameGoesHere"

...so when 2 or more projects are open they don't run each other's same
name code defs. A typical example is the proc that I use to display
help in every project is named "ShowHelp" if using CHM, "ShowHelpHE" if
using EXE. Declaring the containing module as 'private' prevents other
project code from running either of these defs.

The purpose of the sModule constant is to provide an identifier for app
logs. I also use similar in defs so code knows which def is the
'caller'...

Sub MySub()
Const sSource$ = "MySub"
'...code follows
End Sub 'MySub

OR

Function ReadTextFile$(Filename$)
Const sSource$ = "ReadTextFile()"
'...code follows
End Function 'ReadTextFile()

...where the convention is to include parenthesis for functions in order
to distinguish these from subs when reading app logs.

A typical use for def IDs is how I manage Excel settings while code is
running, so defs don't trigger settings inadvertedly...

Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating
.ScreenUpdating = False
AppMode.CalcMode = .Calculation
.Calculation = xlCalculationManual
AppMode.Events = .EnableEvents
.EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub 'EnableFastCode

...which requires the following 'Type' declaration to work correctly.

Type udtAppModes
Events As Boolean
CalcMode As XlCalculation
Display As Boolean
CallerID As String
End Type
Public AppMode As udtAppModes

To use the procedure I just call it from any def and pass the args as
needed...

Sub MySub()
Const sSource$ = "MySub"
EnableFastCode sSource '//turn it on
'...code follows
EnableFastCode sSource, False ''//turn it off
End Sub 'MySub

...and as long as this def has control of those settings they won't get
triggered by other code in the call stack that also uses
'EnableFastCode'!

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"hbj" wrote:
I have a project containing two xlsm files: Accounting.xlsm
and Common.xlsm.
In Accounting I use a VBA procedure to copy row-by-row from
one worksheet to another. I copy dates, numbers an strings.
In the other file, Common, there is a function checking if
a date is within a range and this function is used and called
only in Common.xlsm.
If both workbooks are open when I run the copying routine in
Accounting, the date checking function in Common is called
each time the row data is pasted. In the call stack a
"non basic code" is calling that function.
If only Accounting workbook is open, the copy-paste routine
runs correctly and fast.
How can I trace, where is the call to the function?

Is the question really: how can you prevent date-checking procedure in
Common.xlsm from being called when you are running the copy procedure in
Accounting.xlsm?

In the copy procedure in Accounting.xlsm, try adding the following lines at
the beginning of and exit from the procedu

Sub copyRows()
Application.EnableEvents = False
[.... your code here ....]
Application.EnableEvents = True
End Sub

This assumes that the date-checking "function" is actually an event macro.
Otherwise, it is unclear to me how a date-checking __function__ per se (i.e.
Function checkIt instead of Sub checkIt) could be called during the copy
operation if you are just copying "dates, numbers and strings" and not also
formulas.

Caveat: If you have multiple exits from the procedure, be sure to repeat
the last statement at each exit or "exit" by going to the last statement
(i.e. use Go To). Also, it would be prudent to use an On Error GoTo
statement to be sure the last statement is executed in the event of a
run-time error.

-----

If your question is truly how to see what cell is being copied when the
date-checking procedure is called, try adding the following line at the
beginning of the date-checking procedure in Common.xlsm:

Debug.Print Application.Caller.Address(external:=True)

You can see the Debug.Print output in the Immediate Window by pressing
ctrl+G.

Of course, that might slow down the copy operation significantly. I presume
you will remove the Debug.Print statement after you understand what is
happening.

-----

If neither of those alternatives helps, it might help us to see the actual
files. I confess that I do not fully understand the circumstances, and I'm
reading between the lines.

Upload example files (removing or replacing any personal identifying data)
to a file-sharing website, and post the URL of the "shared" file here. The
following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com

On Wednesday, 12 February 2014 18:59:39 UTC+2, joeu2004 wrote:
"hbj" wrote:

I have a project containing two xlsm files: Accounting.xlsm

and Common.xlsm.

In Accounting I use a VBA procedure to copy row-by-row from

one worksheet to another. I copy dates, numbers an strings.

In the other file, Common, there is a function checking if

a date is within a range and this function is used and called

only in Common.xlsm.

If both workbooks are open when I run the copying routine in

Accounting, the date checking function in Common is called

each time the row data is pasted. In the call stack a

"non basic code" is calling that function.

If only Accounting workbook is open, the copy-paste routine

runs correctly and fast.

How can I trace, where is the call to the function?



Is the question really: how can you prevent date-checking procedure in

Common.xlsm from being called when you are running the copy procedure in

Accounting.xlsm?



In the copy procedure in Accounting.xlsm, try adding the following lines at

the beginning of and exit from the procedu



Sub copyRows()

Application.EnableEvents = False

[.... your code here ....]

Application.EnableEvents = True

End Sub



This assumes that the date-checking "function" is actually an event macro..

Otherwise, it is unclear to me how a date-checking __function__ per se (i..e.

Function checkIt instead of Sub checkIt) could be called during the copy

operation if you are just copying "dates, numbers and strings" and not also

formulas.



Caveat: If you have multiple exits from the procedure, be sure to repeat

the last statement at each exit or "exit" by going to the last statement

(i.e. use Go To). Also, it would be prudent to use an On Error GoTo

statement to be sure the last statement is executed in the event of a

run-time error.



-----



If your question is truly how to see what cell is being copied when the

date-checking procedure is called, try adding the following line at the

beginning of the date-checking procedure in Common.xlsm:



Debug.Print Application.Caller.Address(external:=True)



You can see the Debug.Print output in the Immediate Window by pressing

ctrl+G.



Of course, that might slow down the copy operation significantly. I presume

you will remove the Debug.Print statement after you understand what is

happening.



-----



If neither of those alternatives helps, it might help us to see the actual

files. I confess that I do not fully understand the circumstances, and I'm

reading between the lines.



Upload example files (removing or replacing any personal identifying data)

to a file-sharing website, and post the URL of the "shared" file here. The

following is a list of some free file-sharing websites; or use your own.



Box.Net: http://www.box.net/files

Windows Live Skydrive: http://skydrive.live.com

MediaFi http://www.mediafire.com

FileFactory: http://www.filefactory.com

FileSavr: http://www.filesavr.com

RapidSha http://www.rapidshare.com

Thank you for your replies!

I have now defined Private Module declaration, added Event handling and renamed the modules. Let's see how it works.
However the date handling UDF causes me troubles itself:
It is used as a function called in worksheets cells with parameters
InDate (as date)
OutDate (as date)
Year (as integer)

InDate is a "hardcoded" date value
OutDate is either a calculated date or a "hardcoded" date
Year is a "hardcoded" integer

For each row I search for total amount of days falling between InDate and OutDate the year specified by Year

PartOFYear($A2 as date,$B2 as date, K$1 as integer)

The parameter values can be (4.5.2008, TODAY()+1000, 2008)

When OutDate is "hardcoded", excel calculates it correctly, but when it is i.e. =TODAY()+1000 it fails and requires recalculation F9.

What am I missing?
Håkan