Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

osfameron (3135)

  (email not shown publicly)

Technorati Profile []

Journal of osfameron (3135)

Wednesday May 29, 2002
05:48 AM

The joys of VBA

[ #5267 ]
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
        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
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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • Not bad form (Score:3, Interesting)

    by jdavidb (1361) on 2002.05.29 9:36 (#8896) Homepage Journal

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


    J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
  • 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 violate



  • 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 (