2 workbooks open at same time break macros
 

I have a workbook that has many custom functions built into it. One
of the employees here found a bug that I am not sure how to resolve.
The spreadsheet is used to do estimates for the services we sell. So
we have a template and do a file-save-as to keep record of all the
estimates we have sent out. If you have two of the excel estimate
files open at the same time then the the functions get all messed up
and give you the wrong results in both sheets. Can someone advise me
on where I might start to track down this problem. I assume it has
something to do with the way the functions are setup and maybe even
how the variables are declared within each function, but I am not
sure.

I should note one test I did. I double clicked to open excel then do
file-open to open the first file. Next I double clicked to open excel
again then did file-open to open the second file. This method seems
to work fine, but does not seem like a good solution. It is too easy
to forget that you have to start excel a second time. Plus I would
like to better understand the problem.

Thanks in advance.

Bk

2 workbooks open at same time break macros
 

Well... without actually seeing the code it's impossible to really sa
exactly what's going on, but you're probably exactly right. There ar
ways to point to certain objects that are not specific to any certai
object. For example, you can make changes to "ActiveWorkbook." Thi
is something that works fine if you know for sure that when your cod
is run the workbook you want will definitely be the active one. Ther
are plenty of other cases I could mention that would work or not wor
depending on the currently active workbook or worksheet. Unless yo
feel like posting your code I don't know what to say beyond that othe
than to make it more specific. - Piku

--
Message posted from http://www.ExcelForum.com

2 workbooks open at same time break macros
 

Hi
you're probably not referencing the workbook/worksheet
You may post the relevant part of your macro

--
Regards
Frank Kabel
Frankfurt, Germany


Brian Kranson wrote:
I have a workbook that has many custom functions built into it. One
of the employees here found a bug that I am not sure how to resolve.
The spreadsheet is used to do estimates for the services we sell. So
we have a template and do a file-save-as to keep record of all the
estimates we have sent out. If you have two of the excel estimate
files open at the same time then the the functions get all messed up
and give you the wrong results in both sheets. Can someone advise me
on where I might start to track down this problem. I assume it has
something to do with the way the functions are setup and maybe even
how the variables are declared within each function, but I am not
sure.

I should note one test I did. I double clicked to open excel then do
file-open to open the first file. Next I double clicked to open
excel
again then did file-open to open the second file. This method seems
to work fine, but does not seem like a good solution. It is too easy
to forget that you have to start excel a second time. Plus I would
like to better understand the problem.

Thanks in advance.

Bk

2 workbooks open at same time break macros
 

Brian,

Just to add to the good advice you've already received.

The referencing of objects (workbooks, worksheets, ranges, etc.) may be
ambiguous in the code. Statements like: ActiveSheet, ActiveCell, are based
upon whichever sheet or cell is currently in focus. You may need to consider
tightening up the code so that all of the objects are explicitly defined
within their collections. This isn't to say ActiveSheet is bad, but used
improperly, it can lead to unexpected results.

One very helpful concept is the use of "ThisWorkbook". It specificly refers
to the workbook that owns the macro code that is running. For example the
following line will refer to only one cell, regardless of how many other
workbooks are open:
ThisWorkbook.Worksheets(1).Range("A1").Value

Whereas the following lines can refer to cell A1 in many possible worksheets
or workbooks:
ActiveSheet.Range("A1").Value
Range("A1").Value


Another thought, is that if you can't solve the conflicts (although I would
expect that you could), you may want to put some code in the workbook that
checks to see as the workbook is opened if the current Excel session already
has a similar workbook open. If so, warn the user, instruct them to open it
in another Excel session, and close the second copy of the workbook.
Personally, I would fix the code instead of using this crutch but it is
another layer of protection.

How many lines of code and how many functions are we talking?

Troy

"Brian Kranson" wrote in message
om...
I have a workbook that has many custom functions built into it. One
of the employees here found a bug that I am not sure how to resolve.
The spreadsheet is used to do estimates for the services we sell. So
we have a template and do a file-save-as to keep record of all the
estimates we have sent out. If you have two of the excel estimate
files open at the same time then the the functions get all messed up
and give you the wrong results in both sheets. Can someone advise me
on where I might start to track down this problem. I assume it has
something to do with the way the functions are setup and maybe even
how the variables are declared within each function, but I am not
sure.

I should note one test I did. I double clicked to open excel then do
file-open to open the first file. Next I double clicked to open excel
again then did file-open to open the second file. This method seems
to work fine, but does not seem like a good solution. It is too easy
to forget that you have to start excel a second time. Plus I would
like to better understand the problem.

Thanks in advance.

Bk

2 workbooks open at same time break macros
 

Thanks for the information. That gives me a few places to check and
see if I can get this to work.

Bk

(Brian Kranson) wrote in message . com...
I have a workbook that has many custom functions built into it. One
of the employees here found a bug that I am not sure how to resolve.
The spreadsheet is used to do estimates for the services we sell. So
we have a template and do a file-save-as to keep record of all the
estimates we have sent out. If you have two of the excel estimate
files open at the same time then the the functions get all messed up
and give you the wrong results in both sheets. Can someone advise me
on where I might start to track down this problem. I assume it has
something to do with the way the functions are setup and maybe even
how the variables are declared within each function, but I am not
sure.

I should note one test I did. I double clicked to open excel then do
file-open to open the first file. Next I double clicked to open excel
again then did file-open to open the second file. This method seems
to work fine, but does not seem like a good solution. It is too easy
to forget that you have to start excel a second time. Plus I would
like to better understand the problem.

Thanks in advance.

Bk