×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Encapsulation Problem
2

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

RE: Excel Encapsulation Problem

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

RE: Excel Encapsulation Problem

2
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.

RE: Excel Encapsulation Problem

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

RE: Excel Encapsulation Problem

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

RE: Excel Encapsulation Problem

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").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

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

RE: Excel Encapsulation Problem

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").Select
Worksheets("Sheet 1").Range("MyRange").ClearContents

wk1.Activate





Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources