Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.



Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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.





Reply With Quote
  #4 (permalink)  
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.

Reply With Quote
  #5 (permalink)  
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
Reply With Quote
  #6 (permalink)  
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
Reply With Quote
  #7 (permalink)  
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.


Reply With Quote
  #8 (permalink)  
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
Reply With Quote
  #9 (permalink)  
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
Reply With Quote
  #10 (permalink)  
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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:54 PM.


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