Yet Another -- ExcelCOM UDF

[big_daddy]

@big_daddy - While I appreciate the advice, I hope you can appreciate the fact that I've chosen to model my efforts along different lines.

This is all fine and dandy, but I hope the direction you have chosen turns out to be the better one.

I honestly don't see the need for such beefed-up COM error handling. Functions end up being twice to three times the size they would be without it, and for what? So that I can print some basic COM errors to the console?

Do to these statements I have lost much confidence in your coding abilities. Any half decent programmer knows how important thorough error checking is. A well written program is usually about 70-75% error checking and the rest functionality and comments. This post by Valik touches on how important thorough error handling is.

Furthermore, it has been my philosophy since the beginning of my career to worry about getting the beans into the pot before I worry about cleaning up the kitchen. I have a lot of functions in my UDF simply because they are needed now, not later.

No a better example of what you are doing is throwing a bunch of half cooked food on the table and telling your family to eat.

To illustrate the point - you've had your UDF candidate up for quite some time, and yet I still don't use it because the features I need still aren't there. I'd rather be able to use it and deal with a few potential errors than not be able to use it at all.

Have you made a request for the functionality you need... I didn't think so. To my knowledge every request for additional functionality has been met; let me know if one has been overlooked.

[fisofo]

Come on guys, let's get past this. Perhaps you both can help eachother out a bit... big_daddy can assist in adding error-checking in the excel udf and Locodarwin can help with functionality for the word udf.

Let's develop this stuff until it's worthy to be in the full release! Both UDF's (in my mind) are very much *in development* so let's get them to the next level without making enemies.

Don't want to piss either of you off, just want to see a focus on the programming.

Edited November 3, 2006 by fisofo

[Locodarwin]

Howdy do!

Suddenly I find myself on the defensive end of a very strange and unwelcome debate.

This is all fine and dandy, but I hope the direction you have chosen turns out to be the better one.

What is this, a contest? I didn't sign up for a contest.

When you said to me, in plain English, that you "didn't like [my] function names," I jumped right on it and made the sort of changes you requested. I put considerable effort into it, too.

It was good advice.

Just because I don't think your current advice is applicable to what I'm doing doesn't mean I don't appreciate the fact that you offered it. I do, and said as much. I simply disagree with it.

Do to these statements I have lost much confidence in your coding abilities. Any half decent programmer knows how important thorough error checking is. A well written program is usually about 70-75% error checking and the rest functionality and comments. This post by Valik touches on how important thorough error handling is.

I don't appreciate being subjected to insulting opinions simply because I don't agree with the advice you're trying to force-feed me.

Where on earth did I ever say error checking isn't important? I simply believe there are other ways to code adequate error handling than by installing what I consider an overly-complex error framework for what should be rather straightforward wrapper functions. I base my error-checking more off of the UDFs that have already been added to the UDF library that AutoIt installs with. To my knowldge, only Dale's UDF (recently added) has the framework you've modeled your code after.

I looked it over real carefully, and decided that it would bloat my code too much, and at the same time wouldn't provide a proportionally useful experience. Perhaps with IE it makes sense to have all of that stuff in there - I don't know. I just don't think it fits the bill here.

Furthermore, what happens when someone wants to use your Word UDF and my Excel UDF together? Or mine and Dale's? Yours and Dale's? All that complicated error-checking code becomes completely useless and potentially error-causing for at least one of the UDFs being used.

That's a headache I'd rather not deal with.

Of course, this is all moot, because if you look at my code, you'll see that I do in fact provide a great deal of error checking, especially in the really important places. More is needed in some places, true, but there's plenty there and more to come.

No a better example of what you are doing is throwing a bunch of half cooked food on the table and telling your family to eat.

If you don't like the way I cook, don't eat what I provide. Insulting the cook serves no useful purpose.

Have you made a request for the functionality you need... I didn't think so. To my knowledge every request for additional functionality has been met; let me know if one has been overlooked.

I've written my own Word COM wrappers to the tune of about 80 functions. I haven't posted them out of courtesy to you and your project.

Do you feel threatened, or something? Because your "sage" advice is starting to seem a little forced-upon -- not to mention, belittling.

-S

[big_daddy]

I think you have taken what I meant to be constructive criticism the wrong way. In no way am I trying to make this a contest or force feed any of my opinion on you.

The main reason I modeled my code after Dale's is because I took on the task of getting the documentation for his Library up to UDF Standards. In doing so I gained a strong understanding of how all the error checking worked and was very comfortable with it. Although it may seem over complex, it makes troubleshooting your own and even more so others scripts much easier.

I have used mine and Dale's libraries together without any troubles. The only thing you must remember to do is unregister and register the correct error handlers when switching between libraries.

I meant for my half cooked food comment to be a joke. I rarely use smilies so the humor was lost in the text.

I appreciate you not releasing the word functions, but please do share them with me so that we can get the Word Library closer to being complete. As I have stated many times in my Word Topic, if there's missing functionality, request it, and I will do my best to include it in the next release.

Sorry for any misunderstandings.

Edited November 3, 2006 by big_daddy

[Locodarwin]

Come on guys, let's get past this. Perhaps you both can help eachother out a bit... big_daddy can assist in adding error-checking in the excel udf and Locodarwin can help with functionality for the word udf.

Let's develop this stuff until it's worthy to be in the full release! Both UDF's (in my mind) are very much *in development* so let's get them to the next level without making enemies.

Don't want to piss either of you off, just want to see a focus on the programming.

Now that's sage advice.

I am not going to include the error framework established by Dale at this time. It's not standard across UDFs, and I don't feel it is necessary for what I'm providing. I do not mean to offend anyone who disagrees with this decision.

Big_daddy, I feel your work is a benefit to the community, and now that you've clarified your position, I understand you meant no offense.

I apologize for my own misunderstandings.

-S

[fisofo]

Now that's sage advice.

Thanks

You know what guys? That was awesome. Glad that we're all on the same page now. It's seriously refreshing to be able to work things out in a civilized manner when so many forums are filled with "OMG! U ID10T!!!11!"

This community rocks.

[DaLiMan]

hhmmmm,

and after that it went very very silent......

I hope there is stil some progress on the next release??

It would be a shame to cancel this project after all the effort you put into it...

[Apzo]

It works, it's great, but how can I know if a file is locked ??

I have nor error when I open, modify or save a file while somebody else is working on it.

So the modifications are lost and I have no @error triggered.

Any idea ? Thanks !

Apzo.

[DaLiMan]

It works, it's great, but how can I know if a file is locked ??

I have nor error when I open, modify or save a file while somebody else is working on it.

So the modifications are lost and I have no @error triggered.

Any idea ? Thanks !

Apzo.

I haven't tried it, but maybe this will help you......

http://www.erlandsendata.no/english/index....envbawbwbisopen

[Locodarwin]

It works, it's great, but how can I know if a file is locked ??

Note: this type of "locking" is different from deliberate workbook protection for shared workbooks. My solution here will not work for those situations.

You'll know it's opened by someone else if, after it is opened, the workbook's ReadOnly property has been flagged. So just check it at any time after you open it with something like this:

If $oExcel.ThisWorkbook.ReadOnly Then
    MsgBox(1, "Error!", "This workbook is currently opened as read-only!")
EndIf

You'll know you won't be able to commit any changes if the message box pops up.

I haven't tested this, so give it a go and let me know what happens. Of course, it's up to you how you handle the situation once you've determined that it is read-only, but this example should get you over the hurdle.

I'm investigating ways in which this could be integrated into the UDF. Also, after the next release (which is coming soon, DaLiMan!) I'll be working on integrating shared workbook functionality, as well as protection on the book, sheet, and range levels, plus a whole lot more. And, if you order now....

-S

[Locodarwin]

Updated original post. v1.3 attached. Please see changelog.

A number of goodies!

-S

[Slacker5]

In _ExcelCellFormat, Orientation is spelled incorrectly

.Orientation = $iOrienation

should be

.Orientation = $iOrientation

[Locodarwin]

In _ExcelCellFormat, Orientation is spelled incorrectly

.Orientation = $iOrienation

should be

.Orientation = $iOrientation

Good call, thanks. Fixed library added to first post, version 1.31.

-S

[big_daddy]

Just took a quick look at your code, so far its looking pretty good. However without any documentation it is somewhat intimidating, I can only imagine what it would be like for a newcomer. I think I have mentioned this before, but I would strongly suggest starting on the help file documentation. If you notice on my Word Library the help file is downloaded nearly as much as the library itself.

p.s. If you have any questions about the help files themselves I have plenty experience so ask away.

Edited December 1, 2006 by big_daddy

[Locodarwin]

Just took a quick look at your code, so far its looking pretty good. However without any documentation it is somewhat intimidating, I can only imagine what it would be like for a newcomer. I think I have mentioned this before, but I would strongly suggest starting on the help file documentation. If you notice on my Word Library the help file is downloaded nearly as much as the library itself.

p.s. If you have any questions about the help files themselves I have plenty experience so ask away.

Good idea, thanks. I have help documentation scattered about in text files; when I get the time I'll move them to HTML and compile them into a .chm (I have the AutoIt helpfile source templates). I'll also create the text files JdeB requires for UDF submission as time permits.

I keep a custom au3.user.calltips.api file going for my own use during development; I'll get that out the door soon as well.

Heck, I still have to publish more examples scripts.

-S

[Stanley Lim]

Hi pple,

Please kindly ignore me if this has already been reported... :"> I have found a bug in the _ExcelBookClose function. The bug will cause Excel message alerts to be displayed no matter what value is set to the parameter $fAlerts

Orginal Code:

;===============================================================================
;
; Description:      Closes the active workbook and removes the specified Excel object.
; Syntax:           _ExcelCloseDoc($oExcel, $fSave = 1, $fAlerts = 0)
; Parameter(s):     $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $fSave - Flag for saving the file before closing (0=no save, 1=save)
;                   $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - File exists, overwrite flag not set
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    If $fSave Then
        $oExcel.ActiveWorkBook.Save
    EndIf
    $oExcel.Application.DisplayAlerts = True
    $oExcel.Application.ScreenUpdating = True
    $oExcel.Quit
    Return 1
EndFunc ;==>_ExcelBookClose
oÝ÷ جƥ"+zj/yÛa{ib殶­s` b33c¶ôW6VÂäÆ6FöâäF7ÆÆW'G2ÒG'VP b33c¶ôW6VÂäÆ6Föâå67&VVåWFFærÒG'VP

Cheers!

Edited December 2, 2006 by Stanley Lim

[big_daddy]

When you start working on your help files this may come in handy.

Help File Generator

[Locodarwin]

@Stanley: The problem with not setting .DisplayAlerts back to true before terminating the object is that I've been told it'll save the last value of the property. Therefore, if you set .DisplayAlerts to false and then terminate the object, the next time you open Excel (manually or otherwise), alerts will be off. That's why I reset them to "on" right before termination.

Unfortunately that means if the workbook hasn't been saved, it'll prompt you to save no matter what.

So this is basically a logic error. The workaround is to trick Excel into thinking the document is saved before closing out. This is possible by setting the workbook's .Saved property to true.

I'll post a corrected function shortly.

As a side note, I can't imagine a scenario where you'd go through the trouble to create and populate a workbook via script, only to close it out without saving. Maybe I should remove the save option entirely and just automatically do so on close?

I did notice one other thing. When I reviewed the original function you posted, I noticed a mistake with the function name in the header comments. I'll correct that immediately as well.

@big_daddy: That's a very cool little applet you've made. I imagine I'll be making heavy use of it. Thank you!

-S

[Locodarwin]

Corrected _ExcelBookClose():

;===============================================================================
;
; Description:      Closes the active workbook and removes the specified Excel object.
; Syntax:           _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
; Parameter(s):     $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $fSave - Flag for saving the file before closing (0=no save, 1=save)
;                   $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    If $fSave Then
        $oExcel.ActiveWorkBook.Save
    Else
        $oExcel.ActiveWorkbook.Saved = True
    EndIf
    $oExcel.Application.DisplayAlerts = True
    $oExcel.Application.ScreenUpdating = True
    $oExcel.Quit
    Return 1
EndFunc ;==>_ExcelBookClose

This fix will appear in the next version of the library.

-S

Edited December 2, 2006 by Locodarwin

[Stanley Lim]

@Locodarwin: Thanks for the clarification here. Personally I stumble upon this logic error when I'm debugging my script. Basically, I set my script to run and populate all the data in Excel but not to save it as I don't to overwrite the Excel file that is already on the harddisk.

Thanks and keepup the excellent work!