Excel Encapsulation Problem 2

Hello All!

I just want to post this code because I thought other people might be having the same problem I had. I have had a problem with functions changing the state of the workbook and then returning control to the calling routine. The calling routine then gets confused because the workbook is not in the state it expects it to be in.

An example would be if a routine is looping through the cells on a workbook, then it calls a function that references another page. When the function returns control to the routine, the routine is now on the wrong page of the workbook!

I don't know if I have been missing something, but this has been a big problem for me. Below is a little module that declares 3 functions and one public array to fix this.

The public array holds the state of the workbook (workbook, sheet, and cell). It has room to hold 10 separate workbook states in case you have a lot of nested functions.

The first function adds the current state of the Workbook to the array, and returns the array index for that state so you can refer to it later.

The second function returns the workbook to the state held in that array index.

The third function is just a test to call the other functions.

Option Explicit
Dim States(10, 3)

Function get_state()

Dim workbook As String, sheet As String, cell As String
Static accumulate

accumulate = accumulate + 1
workbook = ActiveWorkbook.Name
sheet = ActiveSheet.Name
cell = ActiveCell.AddressLocal(RowAbsolute:=False, columnabsolute:=False)

States(accumulate, 1) = workbook
States(accumulate, 2) = sheet
States(accumulate, 3) = cell

get_state = accumulate

End Function

Sub set_state(num)

Dim workbook As String, sheet As String, cell As String

workbook = States(num, 1)
sheet = States(num, 2)
cell = States(num, 3)

Workbooks(workbook).Activate
Worksheets(sheet).Activate
Worksheets(sheet).Range(cell).Select

End Sub

Sub test()

Dim statenum
statenum = get_state()

' your code to change to a different workbook, workbook, or sheet here

MsgBox ActiveCell.AddressLocal(RowAbsolute:=False, columnabsolute:=False)

Call set_state(statenum)

End Sub

I hope this helps someone out..
If you use it, let me know and make my day!
Jennifer

 

[Krypster]

Jennifer:

I just read over your code - very well done imo. I haven't used it but probably will because I've had the same problems. Thanks for sharing this with us.

Krypster

 

[dsi]

To avoid this problem, do not select the sheets/cells in your code. You can easily modify them without selecting them. For example, if I am in cell B3 of Sheet1 and want to add a formula to cell C4 of Sheet3:

Dim ws As Worksheet
Set ws = Sheets("Sheet3")
ws.Range("C4") = "Whatever"

If this was run while another sheet/cell was selected, it will remain selected. There are very few instances where you need to actually activate a cell. DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[msmith]

Just want to add a voice to dsi's recommendation. I couldn't agree more. I avoids the problem indicated and your code will always run substantially faster.

Regards,
M. Smith

 

[bscheidel]

There are, however, certain times where it is necessary to select a different worksheet or different cells. I have found these through trial and error, usually due to some bug in Excel 97. For the examples listed below, I have found that it is necessary to select the target worksheet/cells in order for the specified operation to work correctly.

One example is using the UsedRange property:

  Worksheets("Sheet 1").Select
  gLastRow = Worksheets("Sheet 1").UsedRange.Rows.Count

Another example is using the ClearContents function:

  Worksheets("Sheet 1").Select
  Worksheets("Sheet 1").Range("MyRange").ClearContents

Another example is inserting/deleting rows:

  ActiveSheet.Rows(lRowNumber).Select
  Selection.Insert

  Rows(lRowNumber).Select
  Selection.Delete

Brion

 

[jproj]

Sorry Brion, all of those commands will work without selecting the sheet (at least they do in excel 2000). I ran the following macro (with all your examples) and it worked perfectly:

Sub test()
Worksheets("Sheet1&quot.Range("MyRange&quot.ClearContents
Worksheets("Sheet2&quot.Rows(25).Insert
Worksheets("Sheet3&quot.Rows(20).Delete
MsgBox Worksheets("Sheet1&quot.UsedRange.Rows.Count
End Sub

Regards,

jproj

 

[bscheidel]

My experience has been that in Excel 97, those commands will not work unless you first select the worksheet.

 

[yakpol]

bscheidel,
It is true, the function you mention require to select worksheet or range (not always somehow) in Excel 97. You can save the location of the pointer prlor to execution of these functions.

dim wk1 as worksheet
....
set wk1 = ActiveSheet

Worksheets("Sheet 1&quot.Select
Worksheets("Sheet 1&quot.Range("MyRange&quot.ClearContents

wk1.Activate