Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 19th, 2005, 10:39 AM
Registered User
 
Join Date: Jul 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default CurrentRegion troubles

I'm trying to use CurrentRegion for the first time, and it appears it behaves differently when I use it in a sub than when I use it in a function.

This:
Sub SelectCurrentRegion()
    Range("c5").Activate
    MsgBox (ActiveCell.Address)
    MsgBox (ActiveCell.CurrentRegion.Rows.Count)
    MsgBox (ActiveCell.CurrentRegion.Columns.Count)
    ActiveCell.CurrentRegion.Select
End Sub

Tells me what I want to hear - that the CurrentRegion around C5 has 22 rows and 4 columns. The correct region is selected, not matter where in the worksheet is active before I run the macro.

BUT this:
Function TableLookup()
    Range("c5").Activate
    MsgBox (ActiveCell.Address)
    MsgBox (ActiveCell.CurrentRegion.Rows.Count)
    MsgBox (ActiveCell.CurrentRegion.Columns.Count)
    ActiveCell.CurrentRegion.Select
    'TableLookup = "foo"
End Function

Will tell me that the active cell's address is whatever cell I've put the formula in, not C5. Even if I do start it in C5, it tells me that there's just one column and one cell in C5's current region.

Now, the end goal of this function isn't just to select regions, but I can't move on until I learn what I'm doing wrong. Can what I want to do even be done?

 
Old July 20th, 2005, 03:27 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The sub procedure is called from anywhere and the first line moves the activecell to the c5, this is the point where cell c5 is said to "get focus". The cell then continues to be retained as the activecell in focus until the end of the code.

The function (udf or user defined function) executes when you place a cell lets say "d20" into edit mode, this cell is said to "get focus". you type in the function "=tablelookup()" and press enter, the action of pressing enter means that before the cell can "lose focus" it must recalculate the udf, the first line of the udf makes the activecell c5 however because the code in the udf has not finished, the focus is returned to the cell d20 straight after the line completes the execution.

hope this helps.

cheers

Matt
-------------------------------------------------------



 
Old July 20th, 2005, 07:04 AM
Registered User
 
Join Date: Jul 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So, basically, the answer is, "you can't do that"? It's fine if it is, I just want to be sure I'm reading it correctly.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Shader troubles Largo39 BOOK: Professional XNA Game Programming: For Xbox 360 and Windows ISBN: 978-0-470-12677-6 0 August 9th, 2007 03:01 PM
Troubles with Programm AlisaSmi Excel VBA 0 March 7th, 2007 07:50 AM
Database troubles notrosh ASP.NET 2.0 Professional 0 June 13th, 2006 01:20 PM
DataGrid Troubles mrideout BOOK: Beginning ASP.NET 1.0 1 August 17th, 2004 11:32 AM
Interop Troubles Yehuda Pro VB.NET 2002/2003 0 June 29th, 2004 01:26 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.