Excel Encapsulation Problem
Excel Encapsulation Problem
(OP)
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
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





RE: Excel Encapsulation Problem
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
RE: Excel Encapsulation Problem
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.
RE: Excel Encapsulation Problem
Regards,
M. Smith
RE: Excel Encapsulation Problem
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
RE: Excel Encapsulation Problem
Sub test()
Worksheets("Sheet1").Range("MyRange").ClearContents
Worksheets("Sheet2").Rows(25).Insert
Worksheets("Sheet3").Rows(20).Delete
MsgBox Worksheets("Sheet1").UsedRange.Rows.Count
End Sub
Regards,
jproj
RE: Excel Encapsulation Problem
RE: Excel Encapsulation Problem
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").Select
Worksheets("Sheet 1").Range("MyRange").ClearContents
wk1.Activate