Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

New to Perl - is it a better language than VB for this application?

by DyslexicHobo (Initiate)
on Mar 20, 2013 at 04:12 UTC ( #1024429=perlquestion: print w/replies, xml ) Need Help??
DyslexicHobo has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I am a new Perl user (and generally an inexperienced programmer). For my work, we use very poorly-coded excel macros daily to execute tasks such as comparing the contents of two excel documents, making sure that excel documents contain certain information, and checking that files with specific file names reside in the correct Windows directories. Some of these macros can take up to 15 minutes to run. It seems like these tasks should be computationally simple, and it's my instinct that it takes so long due to the fact that it's coded in Visual Basic. These macros were not coded by professional programmers, but by engineers who were only mildly experienced with VB. Would it be likely that if these macros, doing the same tasks, would be faster to run if coded in Perl? I'm interested in taking on a project that would be useful and is within my scope as a beginning Perl programmer.
  • Comment on New to Perl - is it a better language than VB for this application?

Replies are listed 'Best First'.
Re: New to Perl - is it a better language than VB for this application?
by NetWallah (Abbot) on Mar 20, 2013 at 05:44 UTC
    Excel document manipulation related tasks are NOT likely to be faster in perl.

    If you can attribute the 15-minute duration to managing a large quantity of data (Thousands of Excel rows), then you should consider moving the data into a database - just doing that would probably yield orders of magnitude improvement.

    If you attribute the slowness to poor code, well, bad perl code can run slow too, so the focus should be on improving the code.

    Having worked similar issues before, I have mostly encountered attempts to manipulate data that is better suited to a database.

                 "I'm fairly sure if they took porn off the Internet, there'd only be one website left, and it'd be called 'Bring Back the Porn!'"
            -- Dr. Cox, Scrubs

Re: New to Perl - is it a better language than VB for this application?
by davies (Parson) on Mar 20, 2013 at 08:22 UTC

    What features of Perl are you planning to use that aren't available in VBA? If there aren't any, stick to VBA. It's a ghastly language, but it's more readable to the beginner than Perl and the interface to Excel is slightly cleaner (see my reply to Extracting Chinese characters from Excel for a situation where VBA can but Perl can't). Remember that if you are trying to recruit someone to maintain the code, they will have to know Excel and $language. That group is much larger (or at least thinks it is) for $language = VBA than for $language = Perl.

    But yes, there are plenty of situations where Perl has the edge. That's why I'm here! And there are at least tens of thousands of accountants (my field) who think that recording a macro is programming and end up producing screeds of inefficient code that is utter rubbish and totally unmaintainable. Look out for telltale signs such as Select and Activate. If you don't know why that's bad, read Excel’s Select and Activate considered harmful.

    One inefficiency I can warn you about in either language: passing data into and out of Excel is hideously slow. If you can, read and write blocks with single statements by passing arrays (I don't think I've demonstrated this in any of my code here, but it's a common technique). Certainly minimise your transfers and don't use Excel cells as variables.


    John Davies

Re: New to Perl - is it a better language than VB for this application?
by kcott (Chancellor) on Mar 20, 2013 at 05:00 UTC

    G'day DyslexicHobo,

    Welcome to the monastery.

    I've coded those sorts of tasks in Perl so it's certainly possible to do; I can't make any claims about whether it will be faster.

    For various tasks, you'll find there's different modules for *.xls and *.xlsx spreadsheets, e.g. Spreadsheet::ParseExcel (for the reading the older format) and Spreadsheet::XLSX (for the newer format).

    There's a tutorial (with a lot of follow-up information) that may address many of your initial questions: Using Win32::OLE and Excel - Tips and Tricks.

    -- Ken

Re: New to Perl - is it a better language than VB for this application?
by vinoth.ree (Monsignor) on Mar 20, 2013 at 04:59 UTC
Re: New to Perl - is it a better language than VB for this application?
by soonix (Prior) on Mar 20, 2013 at 06:29 UTC

    Similiar to what NetWallah said:
      One can write bad code in any language.
    If you have perl do the same thing the same way, it will run crawl at the same speed.

    More of importance is: which language is/will be easier for the person(s) to maintain these macros, especially if there are frequent change requests.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1024429]
Approved by kcott
[erix]: there used to be the 'razing' of towns and citoes but that now means something else in Germany (rasen=driving too fast)

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (6)
As of 2017-05-29 15:34 GMT
Find Nodes?
    Voting Booth?