All the Perl that's Practical to Extract and Report

The joys of VBA

osfameron on 2002-05-29T10:48:08

I suppose it is bad form to write an off-topic post on my first use.perl journal entry. Well, not quite off topic, as it's about me struggling to use VBA in Excel to do things the Perlish way.

First off, there are no Regular Expressions (you can import them from WSH version 2, but that's not installed on our corporate build, so no joy).

Also, I'm having fun implementing basic functionality... Here are some list munging functions. Actually, they don't use VB's braindead arrays, but the "Collection" object, which is slightly less braindead, and more like a Perl list. 

Function split(split_string As String, orig_string As String) As Collection
  'OK not really like Perl split, 
  'e.g. only split on literals
  Dim coll As New Collection
  Dim pos
  pos = 1
  Dim my_string As String
  my_string = orig_string
  While my_string <> "" And pos <> 0
    pos = InStr(1, my_string, split_string)
    If pos > 0 Then
        coll.Add Left(my_string, pos - 1)
        my_string = mid(my_string, pos + Len(split_string))
    End If
  Wend
        coll.Add my_string
  Set split = coll
End Function
And the positively minimalist 'join' function. 
Function join(jstring As String, coll As Collection) As String
    Dim token As Variant
        For Each token In coll
        If join <> "" Then join = join & jstring
        join = join & token
    Next
End Function
To compare with Perl ("Make easy things easy, and hard things possible"), VBA is very good for manipulating the Excel object model and can clearly do many very difficult things easily. It just falls down in some simple things, which appear to be impossible or at least pointlessly difficult.

Not bad form

jdavidb on 2002-05-29T14:36:45

In fact, it's par for the course. I can say that, because I did it, too. :)

Welcome.

VBA library of Perlish functions (list utilities)

osfameron on 2002-05-30T08:46:32

At this rate, I might make VBA almost usable, though I doubt it as there are so many deficiencies in this braindead language, so many things that it's designed not to be able to do...

I've created a VBA module with a number of Perllike list utilities - makeList, makeHash, split, join, push, pop, shift etc.. I was going to post the module for your interest, (if you're using VBA, or just want to laugh at the tortuous syntax) but I got this use.perl error:

Lameness filter encountered.
Your comment violated the "postercomment" compression filter. Try less whitespace and/or less repetition. Comment aborted.
How appropriate. If you want a copy of this, give me a shout (/msg me on perlmonks for example).

language mottoes...

jweveland on 2002-05-30T14:53:02

Your closing got me to thinking... so far we have:
  • perl - Makes easy things easy and hard things possible
  • VBA - Makes hard things easy and easy things hard to impossible
And the following, avaliable for assignment:
  • Makes easy things hard, and hard things impossible
  • Makes easy things impossible, and hard things unthinkable
  • Makes easy things require 367 .dll files, 6 reboots, and a bluescreen every 5 minutes thereafter
  • Makes "Hello, world" into a 40 line program
  • Makes strings into integers, four per byte (five on DEC-10s/20s)