Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2
This is the forum to discuss the Wrox book Excel 2007 VBA Programmer's Reference by John Green, Stephen Bullen, Rob Bovey, Michael Alexander; ISBN: 9780470046432
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 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 September 1st, 2007, 02:42 PM
Authorized User
 
Join Date: Sep 2007
Location: Carson City, NV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel 2007 VBA PRogrammers Reference

Hi.
I recently obtained a copy of your book, and have begun trying some of the sample code.
I've run across an item that I am having trouble with-- I'm not a seasoned programmer.
On page 329, chapter 15, you've given some sample code for showing all of the face ID's for excel.
I wrote it up-- and made sure that I got all of my mis-typings-- and when I went to compile the sub, I got an error.
"Sub, Function, or Property not defined (Error 35)"
It appears that the compiler does not like the statement:
IsEmptyWorksheet(ActiveSheet)
I've had this kind of thing before with older macros from Excel 2000, and am wondering if it's a difference from Excel 2000/2003 to 2007.
Eg., Application.Run does not work in 2007, and we must use Call ....
(that took about 3 hours to figure that one out.)
Is there some other phrase that will work for 2007?

Thanks for the book. It's been really helpful so far.



 
Old September 5th, 2007, 08:08 AM
Registered User
 
Join Date: Sep 2007
Location: Alamogordo, NM, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I had the same results as you. I was able to remove the code that caused the problem and the code still worked. I too have little experience. Its Interesting to me, not so much that the code sometimes burps, but mainly their seems to be a lack of chalk board examples. What I am trying to say is, when writing tech books, trying to understand how the puzzle fits together, can be a real problem. If the authors were using flow charts to explain how variables are used in their code, I know beginners like me could grasp a greater understanding of the Architecture when designing the modules, C-modules, procedures and the various code structures contained.
It seems most book writers are using screen-shots, which are great for reviewing the way the code should look in the VBE, but sure doesn't do much for designing the Architecture. To me it's like looking for treasure with out a treasure map. Your Though's?

William l McNulty
 
Old September 5th, 2007, 01:23 PM
Authorized User
 
Join Date: Sep 2007
Location: Carson City, NV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the response.
So all you did was to remove the IsEmptyWorksheet(...) phrase and it worked fine?
Flowcharts don't work for me. Years ago I took a C++ class and the prof used flowcharts. While I could easily make sense of them because the logic was fairly clear, it didn't help me with just trying to understand the language.
I suppose it's just a lack of exposure.
The one thing that I have found is the more code/macros I write, and try various things, the more sense it makes. Which is of course what the prof, and TA's had said after lectures.
I.e., I'm able to do far more than I could just two years ago, but as usual, there's always room for improvement.





 
Old September 5th, 2007, 01:59 PM
Authorized User
 
Join Date: Sep 2007
Location: Carson City, NV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, apparently that was it.
For all future visitors who come asking about the Face ID Sub in chapter 15, page 329 of the Excel 2007 VBA programmer's reference guide:
Comment the following line:
If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub

with a single quote mark-- in case you weren't aware on how to comment.
The routine does in fact work, and gives closer to 16400 (than the "over 15000" stated in the book) Face ID's. As stated in the text many are repeated. Essentially all of the icons used in office are posted by this routine. It also loads all of the images.
I'm running a PIII-600, with XP-ProSP2, and 544mb of ram. It took approx. 3-5 minutes to run from start to finish.
And that was with my email client open/minimized, this webpage was minimized, and the Excel window in a state of freeze-- as called by the code. I was however able to watch the FaceID count take place in the lower left corner of my Excel window.
Perhaps one of the authors can visit, and explain why this function wasn't defined, and how to define it so that we won't attempt to overwrite an occupied worksheet.
Of course now that I know what's what here, I won't do it again in that manner. Still, an explanation would be nice.

 
Old September 6th, 2007, 08:44 AM
Registered User
 
Join Date: Sep 2007
Location: Alamogordo, NM, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I Tested this code again this morning and it looped 16200 faces. I can't explain the glitch the first time I created it, except that it was related to the EmptyWorksheet. I know I had a problem and removed some of the code but it worked the first time this morning. I was hoping to recreate the problem so I could review the code for err's. See if this is the code you have? Copy and paste this code and try it again. By now you should have all the code from the commandBarsWorksheet "FirstLevel" "AllLevels" & FaceId code is that correct?



Sub ListAllFaces()
Dim iFaceId As Integer ' Tracks Current FaceID
Dim iColumn As Integer ' Tracks Current column in worksheet
Dim iRow As Integer ' Tracks current Row in worksheet
Dim ctl As CommandBarControl
Dim cbr As CommandBar
    If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub
    On Error GoTo Recover
Application.ScreenUpdating = False
Set cbr = CommandBars.Add(Position:=msoBarFloating, MenuBar:=False, temporary:=True) Set ctl = cbr.Controls.Add(Type:=msoControlButton, temporary:=True)
    iRow = 1
    Do
        For iColumn = 1 To 10
        iFaceId = iFaceId + 1
        Application.StatusBar = "FaceID = " & iFaceId
        ctl.FaceId = iFaceId
        ctl.CopyFace
        ActiveSheet.Paste Cells(iRow, iColumn + 1)
        Cells(iRow, iColumn).Value = iFaceId
        Next iColumn
        iRow = iRow + 1
    Loop
Recover:
If Err.Number = 1004 Then Resume Next
Application.StatusBar = False
cbr.Delete



End Sub

Bill <"M"


William l McNulty
 
Old September 6th, 2007, 09:53 AM
Registered User
 
Join Date: Sep 2007
Location: Alamogordo, NM, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Great News

Glad you got the code going.
I am going through another book at the same time called "Professional Excel Development"
Both books have some simular information and yet because they are written by different authors for different purpose, I often-times get interesting, yet different views on the same subject and of course different examples.
Dictator Applications was given an entire Chapter. Dictator Applications take over the hole Excel Environment. I'm breaking new ground for me, it's been interesting to say the least.

I know exactly what you mean about code and catching on, the more you do it, the more it has meaning.





William l McNulty
 
Old September 6th, 2007, 12:47 PM
Authorized User
 
Join Date: Sep 2007
Location: Carson City, NV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bill,
You should be able to set a "watch" in the VBA-IDE, and track what's taking place.
Since however I'm not real "seasoned" at this, I can't say what to do when you actually find the issue.
Hence my hope that an author, or very seasoned programmer would step up and tell me/us what's taking place, and why.
What I can say is the error I got-- posted above-- states the phrase was not defined.
Which makes me think that it's not a built-in Excel function.
Therefore, it'd need to be defined in the Dim section of the macro, and then perhaps be a "stand-alone" macro which states its function, goal.
I.e.,
Dim IsEmptyWorksheet As Boolean(perhaps?)
IsEmptyWorksheet = active.worksheet
If IsEmptyWorksheet is empty/blank/etc...
Ok to proceed.
End If
......
But, I have no idea. It'd be some variation on that.
However.... as noted, since it works when commented out, I suppose that the real issue is that its goal was to prevent the user from over writing previous work.


 
Old September 7th, 2007, 09:01 PM
Registered User
 
Join Date: Sep 2007
Location: Alamogordo, NM, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Steve

This is the answer given to me

It doesn't need a dim statement, but you do need to have a function with that
name.

Do you have a function like that that the calling sub can find?

If no, maybe you can use this one:

Function IsEmptyWorksheet(wks As Worksheet) As Boolean
    If wks.UsedRange.Address = "$A$1" _
     And IsEmpty(wks.Range("A1").Value) Then
        IsEmptyWorksheet = True
    Else
        IsEmptyWorksheet = False
    End If
End Function

Here are some alternatives, too:

Doug Glancy's:
http://www.dicks-blog.com/archives/2...-viewer-addin/

John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip67.htm

Jim Rech's:
http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech

Jim's version for the Office2007 button images can be found on Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
or directly from:
http://www.rondebruin.nl/files/BtnImages.zip


Sunday88310 wrote:
>
> Question is why (IsEmptyWorksheet) can hang up the running of the application
> does this statement need a Dim
> This statement produces all the system faces and takes 3 minutes to run.
>


William l McNulty
 
Old September 7th, 2007, 09:16 PM
Registered User
 
Join Date: Sep 2007
Location: Alamogordo, NM, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tested the code and I get "Ambiguous code" as a pop up so that didn't seem to work. I will review a few of the other sites listed and let you know what I find if your still interested?

Bill

William l McNulty
 
Old November 2nd, 2007, 10:10 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

"Eg., Application.Run does not work in 2007, and we must use Call ....
(that took about 3 hours to figure that one out.)"

Its been more than 3 hours and I still have nt figured out :(

Whats the replacement for application.run in excel 2007?





Similar Threads
Thread Thread Starter Forum Replies Last Post
excel 2007 vba UDF DavidReese BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 1 February 24th, 2009 01:10 AM
VBA Print preview in Excel 2007 solnajeff Excel VBA 1 June 23rd, 2008 01:21 PM
Excel 2002 VBA Programmer's Reference hawlk VB How-To 0 February 4th, 2007 04:35 PM
Excel 2002 VBA Programmers Guide quinoman Wrox Book Feedback 1 June 9th, 2003 09:07 AM





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