Do you use Excel 4.0 macro functions?

[Jon von der Heyden]

Do you use Excel 4.0 macro functions?

Just wondering how many of you use this. If so what do you commonly use XLM4 functions for? And if not why not?

For instance, we regularly receive questions on how to add up cells based on fill color. Though using umpteen colors in my models is not my preferred design, I frequently inherit color coded models that demand calculations based on color.

Personally I opt for GET.CELL and base my calculations around this method using Names. But then looking on the board most of the guru's prefer to suggest using a UDF. Not sure what the reasoning is behind this? From my perspective GET.CELL is alot easier than learning VBA syntax etc.

I know some run UDF's in add-ins, but again I see this as more of a problem since when I distribute the workbooks using the UDF's the users always receive #NAME? errors.

Any thoughts / comments?

Regards
Jon

 

[SydneyGeek]

No, I don't use XLM macros. Maybe that's just habit but I have found that I can do pretty much whatever I need using VBA. And one macro language for Excel is enough for me...

However, I remember reading a really cool article by Juan Pablo González, showing how do Page Setup with XLM. Because it doesn't communicate via the printer driver it is significantly quicker than VBA for the same job. So, maybe there's a case for keeping XLM as an option.

Denis

 

[Jon von der Heyden]

Thanks for your feedback Denis.

I can't say that I'm an expert on XLM4 but do occassionally execute functions within my VBA projects.

E.g:

ExecuteExcel4Macro("GET.DOCUMENT(10)")

This will return the number of the last used row, which I find easier than some of the other methods.

Cells(Rows.Count, 6).End(xlUp).Row

I see this method used quite often, but of course this will always consider the last row as per the last item in a list in column F.

Cells.Find (What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Whilst this method would return a more TRUE last row but is considerably longer than the XLM4 method.

Regards
Jon

 

[Smitty]

I use them infrequently, but they are handy when I do.

Specifically on the fly dialog sheets.

I'll have to find Juan's page setup article now.

Although I agree with Denis that one language is more than enough, especially now with VSTO...

Smitty

 

[Juan Pablo González]

Who, me? I remember writing that for this book, but I don't remember an article on that online... maybe at DDoE ?

 

[Richard Schollar]

I remember reading that specific tip in Bullen et als seminal Professional Excel Development - and boy does it make page set up faster

 

[erik.van.geit]

Hi,

about fast page setup
http://www.mrexcel.com/board2/viewtopic.php?t=237503
sort by color
http://www.mrexcel.com/board2/viewtopic.php?p=1117744

I use XLMmacros mainly to count colors*. It's way faster than Chip Pearsons method which is often refered to.

kind regards,
Erik

* to be honnest I do not "use" them, since I never counted by color thusfar in any of my projects. I use them to answer questions on the Board

 

[Jon von der Heyden]

So in conclusion not many guru's have a passion for XLM4 macros! Of course apart from Juan's clever fast page set-up.

Also, from what I can gather using XLM4 to form calculations around colors is still quite valuable and no-one has highlighted advantages of using VBA UDF's over this, so I think I should continue as before.

Thanks everybody for feedback.

Regards
Jon

 

[erik.van.geit]

So in conclusion not many guru's have a passion for XLM4 macros!

That would be a rather unscientifical conclusion.
Not many "guru's" did respond to this thread, would be more correct.

 

[Jon von der Heyden]

Which could be interpreted as not many Guru's are interested in XLM4