Intro story and asking for an Excel UDF example

[Jemboy]

Yesterday I had to make some little changes to an old Autoit program we use at my work.
The program reads some data and convert it to Excel.
Before writing the cell, it is changed to text and later on I slap the column with an autofitwidth.
Furthermore weI execute a conditional format on the sheet, to make the data more readable.

I quickly found out that because of the breaking changes Excel.udf had starting from AutoIt 3.3.12.0,
a lot of things had to been changed.
The changes I had to do, only took 10 minutes.
After trying to adjust the script for over 5 hours, to get it working with the new Excel.udf, I gave up.

I stopped changing the script, uninstalled the my Autoit and went looking for an older version.
Luckilly I was able to find Autoit v3.3.8.1 (with corresponding Scite) in my software repository.

Installing Autoit V3.3.8.1 and compiling the file, now took me  10 minutes .

 

So why did I not get the old script working with the new Excel.udf?

There are several reasons I failed getting the old script working with the new Excel UDF.

1.  I had some pressure from management to fix it ASAP (and got a little anxious) 
2. Most all resources on the internet point to the working of the old EXcel.UDF
3. And offcourse there were thosing "breaking changes",
   with new functions using diffrent parameters or using parameters in different order.

One of the column's on the sheet is used to store EAN13 (barcode) and was formatted like 1,23E12.
I couldn't change the cell to text, also autofitwidt was not working and using conditional formatting was also a no-no.
So in the end I could use the new Excel UDF, but not desapointed management.

 

What would I like to ask?
I understand that sometimes you want to rewrite a program to make it better. I even understand that one has to make breaking changes sometimes.
But in this case because of lacking examples/resources my day went completely down the drain.

I would like to ask the Excel.udf developpers to:

1. Make more functions available to do things like changing cell properties easily, changing cell color, do an autofit columnwidth, format data conditionally. 
    
2. Or write an Example using the (new) Excel UDF, making examples how to format a cell, do conditional format, changing cell colors etc.
    

I probably am more of an example guy.
Having a good Excel.UDF Example showing a lot of common things normally makes, programming things easy for me.
Because I can keep tweaking snippets until I get it working the way I want it.

 

So dear developpers, could you help me and other future user out?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[water]

Did you have a look at the wiki? For some/most of your questions you should find an answer there.

[water]

At the time when the Excel UDF was rewritten some of the 3.3.8.1 functions were simply a wrapper to a single command. It was requested to drop those functions as there was a big overhead and on the other hand they didn't cover all possible formatting .

So this information was moved to the wiki where it can easily be extended

$oWorkbook.ActiveSheet.Range("A:C").NumberFormat = "@" ; Formats columns A to C of the active sheet as string
$oWorkbook.ActiveSheet.Range("A:C").Columns.AutoFit ; Set the width of columns A to C to AutoFit

 

Edited August 31, 2018 by water

[AdamUL]

@Jemboy  In relation to using old versions of AutoIt.  You can run multiple versions of AutoIt without uninstalling the latest version, or installing older versions.  I have old versions for such this type of situation you had.  You can have the latest version installed, and get the older version zip files from the archive.  You can then extract them to a directory you want to use them.  I prefer the AutoIt installation directory with the version number for folder name from each.  You can then use the #AutoIt3Wrapper_Autoit3Dir to tell SciTE which version of AutoIt to use in what file directory.  Examples below.  

;~ #AutoIt3Wrapper_Autoit3Dir=C:\Program Files (x86)\AutoIt3\3.3.8.1  ;Optionally override the AutoIt3 install directory to use.
;~ #AutoIt3Wrapper_Autoit3Dir=C:\Program Files (x86)\AutoIt3\3.3.12.0  ;Optionally override the AutoIt3 install directory to use.
;~ #AutoIt3Wrapper_Autoit3Dir=C:\Program Files (x86)\AutoIt3\3.3.14.2  ;Optionally override the AutoIt3 install directory to use.
#AutoIt3Wrapper_Autoit3Dir=C:\Program Files (x86)\AutoIt3\3.3.14.5  ;Optionally override the AutoIt3 install directory to use.

 

Adam

 

[Jemboy]

@AdamUL, thanks for pointing this out! Working on other scripts at the same time, I kept uninstalling and again installing the diffirent versions. Your solution will make my life a lot easier in the future! 

[Jemboy]

@Water: Yes, I did get a look at the wiki (that's how I found out the breaking changes ), but for me the wiki is a little to theorotic. I like the have hands on examples I can tweak.
And I must admit, the previous Excel.udf was somewhat sluggisch but it worked as a charm.

For now the fire has been "extinguished", but I will look at your commands for autofit and format the cell to text, in the near future.
Would you consider adding an working example script to the wiki showing how the new UDF works with normal things like making text bold, changing color etc. (all the things explained in the wiki.

As stated earlier almost all forum questions and internet postings about the Excel.udf are old postings. So most asked questions have solutions using the old UDF.
It's a matter of time, but time is of the essence

Cheers.

 

[water]

The wiki has code snippets for every explained formatting option. Adding full working examples would only repeat code you should already know.

Example:
Now you find

$oRange.Font.Size = 12

A full working script would look like:

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkBook = _Excel_BookNew($oExcel)
Global $oRange = $oWorkbook.Activesheet.Range("A1")
$oRange.Font.Size = 12

Each formatting option would add this overhead.

So for the time being I don't think it is sensible to add full working examples

Edited September 7, 2018 by water

[Jemboy]

@water: I still believe a big Excel.udf example would bring down the learning curve to learn how to use the UDF.
I myself learn the most by tweaking the diffirent parameters and adding "Msgboxes" to check variable values.
Also objects are not my forté, for I am from the pre object area 

But thanks for posting a basic Excel framework I can use in the future.

 

[TheDcoder]

I agree that there is a need of a good example as quite a bit of Excel COM is involved, usually it is hard to find documentation for COM compared to finding documentation in AutoIt UDFs...

[water]

We have a lot of sources where you can get information from:

• Every function of the Excel UDF comes with at least one example
• A lot of functions that the Excel UDF does not cover are explained in the wiki - including code snippets to include in your script.
  As the Excel UDF is just a wrapper for Microsofts Excel COM you need some understanding how this works to use the code snippets.
• For everything else you need to get your feet wet with the MS documentation of the Excel object model (example for Excel 2013)

If there is some urgent need for functions which the UDF and the wiki do not cover at the moment I might add them to the wiki.
Please post now

[TheDcoder]

@water Can't say I disagree, thanks for the work

[Jemboy]

@water: For new fuctions, I would suggest (universal) functions:

• to format cells (color, hight, font, background etc.),
• turn on/off filter,
• filter certain column conditionally (e.g. color, containing..., sort A-Z, Z-A_
• conditional formatting
• freeze row / column
• importing data e.g. CSV

Basically what most people do manually.

As an example, every month we download  a CSV from our VOIP-provider. It contains every call made during the month.
However the file also contains the calls from multiple locations.
In the future, I would like to make a script to do:

 select csv, import into Excel, make phone numbers text, turn on filter, freeze first row, show only calls from 1 number/location,
format the sheet conditionally (so not to strain the eyes), do this for all locations, and mail the files to the location managers.

 

 

 

[water]

• to format cells (color, hight, font, background etc.) => can be found in the wiki
• turn on/off filter => see example script for _Excel_FilterSet
• filter certain column conditionally (e.g. color, containing..., sort A-Z, Z-A) => Need to do some investigation if this can be solved using _Excel_RangeFilterSet
• conditional formatting => can be quite complex. As you are the first asking for this functionality I'm not quite  sure it is worth the effort to modify the UDF.
• freeze row / column => can be found in the wiki
• importing data e.g. CSV => See _Excel_BookOpen or for advanced import _Excel_BookOpenText

If you need a special function not covered by the Excel UDF you can always start the macro recorder, do what you want to automate, stop the recorder and then translate the resulting VBA code to AutoIt.
Or create a template workbook by hand and just fill in the data using AutoIt.