

Ask HN: A high level language that compiles to VBA ? - zrz

I don&#x27;t have a lot of background in computer science (4 years), but it seems to me that recently, we saw some new languages coming alive.I just heard of Elixir which is built on top of the Erlang VM. Scala is a little bit older (2003) and targets JAVA VM. Clojure(2007) targeting JAVA VM and being compilable to javascript, seems to be a really clever move.<p>Javascript rising popularity have triggered the rise of transpilers that targets that language.<p>My point is that designing high level language that transpiles to another seems to be a pretty active field. I guess that tools to do so have evolved in the recent history (I actually would be interested to know about the state of the art, any resources are very welcome). Leveraging an existing platform by adding concept-comprehension at the syntactic level (i&#x27;m thinking of list comprehension, for example) seems to add productivity at no cost, no trade-off (?)<p>OK now, the real topic.<p>In my opinion, VBA is as annoying to write as it is present in the industry. A lot of companies rely on it (I&#x27;m working in the financial industry, so mainly VBA for Excel).<p>A lot of people uses VBA, a lot of people already have that tool, and struggle to use it,and still use it. VBA allows you to do what you want to do, but I don&#x27;t think it is optimal to directly write VBA.<p>I think of it as a telegraph line: the network is already there, Morse code allows you to write anything you want, but it a PITA to work with. Build a tool that automatically encodes and decodes what&#x27;s on the channel and you&#x27;re on the internet (OK, oversimplifying here).<p>Can&#x27;t we put excitement back in VBA technology by hiding it ? I&#x27;m sure we have tools to make it happen.<p>I really would love to have developer&#x27;s opinion on that. If you think that nobody has ever been that wrong, please include enlightenments.
======
dalke
This question comes across as rather meandering, and I wasn't sure what you
wanted. As far as I can tell, you've asked only one question: "Can't we put
excitement back in VBA technology by hiding it ?"

No. That is, why should it? You can write native components as a DLL using OLE
Automation, and Microsoft wants people to use .NET. Why target VBA when you
can target C#?

"it seems to me that recently, we saw some new languages coming alive" \-
That's a sign of your inexperience with the field. Transpiling has been around
for a long time, and is a frequent way to boostrap a new language. For
example, the cfront implementation of C++ used to compile to C, and many
Eiffel languages do the same.

For one of the most advanced projects, see PyPy, which is a system for
producing JITs for different language front-ends (the most popular is Python)
and backends (the most popular is x86 but Javascript is also possible).

~~~
zrz
'why should it?' because VBA feels horribly wrong to write, at the opposite of
intuition. One can say that this is just my own opinion, but i would be
curious to talk to somebody that thinks 'it's like VBA reads my mind!'. When I
I switched to coffeescript and less, I wasn't even unsatisfied with js and
CSS. But, writing it, I always think they took productivity a step forward. If
you can avoid writing things that are implicit, it lowers the probability of
faulty code. it saves you time and pain. that is just smart.

You mention dlls, C#, PyPy ... those are big tools for writing whole projects.
They implicitly request the use of an IDE for the compilation/linking steps.
This is definitely not the solution i,'m looking for.

~~~
dalke
I can't reconcile "it's like VBA reads my mind" with the goal, since it
wouldn't be VBA; it would be another language which compiles to VBA.

CoffeeScript isn't "Javascript that reads your mind." Scala isn't "Java that
reads your mind."

CoffeeScript had some difficulties, for that matter. It wasn't until
CoffeeScript 1.6.1 that there was a way, in collaboration with browsers which
support source maps, to get error line numbers relative to the CoffeeScript
source instead of the intermediate Javascript.

Any language which isn't VB but which compiles to VB will have the same
problems - how do you report report run-time errors in a way that your users
can understand them?

There's similar problems with debugging. How do you step through your source?
How do you inspect intermediate values? These are things that VBA supports,
but your language won't be able to support.

You might end up with a great language design that ends up taking more time to
implement in because it isn't debuggable.

Regarding PyPy, the reason I mentioned it was in context of "I actually would
be interested to know about the state of the art [in designing high level
language that transpiles to another], any resources are very welcome." PyPy is
state of the art for that, if you want a JIT.

~~~
zrz
Do you mean that PyPy is a state of the art transpiled language? Actually I
was looking for state of the art tools that helps designing/implementing
languages.

I have come across CoffeeScript's annotated source, on their website, and I'm
definitely going to digg deep in this documentation.

I also might get more information on the difficulties that appeared at the
creation of CoffeeScript, as you say.

Although, I am thinking about a language that compiles to VBA, meaning it
produces visible VBA, and that VBA would be debuggable I guess.

~~~
dalke
I mean that RPython toolchain of PyPy is a language implementation platform.
It has multiple back ends. The most common is x86, but one of the more
experimental backends is Javascript. There are several languages implemented
in PyPy. The most well known is Python, but Ruby, Prolog, and others exist.

Thus, PyPy is a transpiler from, say, Python to Javascript, etc.

LLVM is another such tool. It supports an asm.js backend, which gives quite
good performance in modern Javascript engines. The end result is that you can
compile C and other languages into Javascript. See, for example, the Doom port
for the browser.

------
bdfh42
I suspect that the problem here would be that VBA in Excel is all about the
environment (of Excel) and the manipulation of Excel objects and that this
would make it an unsuitable target for cross compilation - which is generally
successful only for general purpose languages.

Your "source" language would have to be so constrained by the limits and
peculiarities of Excel that it would be as hobbled and as difficult to write
well as VBA.

[Edit] Joel Spolsky's FogBugz team did something interesting in this area
though (see [http://www.codinghorror.com/blog/2006/09/has-joel-spolsky-
ju...](http://www.codinghorror.com/blog/2006/09/has-joel-spolsky-jumped-the-
shark.html) for a take on that) but there again he was pretty responsible for
the development of VBA when he was at Microsoft.

~~~
zrz
That's the kind of answers I was looking for. Does the nature of Excel VBA
limits the possibilities of making it simple ?

The fact of it being all about the environment means to me that there is room
for what I called 'concept-comprehension at the syntactic level'. I mean, I
can see myself manipulating Ranges and Arrays in a lot more intuitive way, and
that's, in my short experience, the main use of macros.

Come on, Excel is a spreadsheet software, it's basically meant to be use for
numeric arrays manipulation and it does not offers any syntactic sugar around
array manipulation.

~~~
gruseom
If you want to write code to manipulate numeric arrays, why not use an
existing high-level language that is good for this? In other words, why are
you using Excel?

Not suggesting that you shouldn't; just curious to hear what benefits Excel
has for you that keep you using it past its sweet spot.

~~~
zrz
Writing VBA code for users that can't is part of my everyday job, and I guess
that it is the job of a lot of support/commando developers. Excel is a great
tool foto implement simple investment strategies but it often ends like 'hey
could you write what we are trying to do in VBA ?', and most of the time it
can be done. But I have that feeling that writing VBA costs more than it
should, that VBA is playing against the developer while any other language
makes him feel like there's no limits.

~~~
gruseom
What kinds of things are they trying to do that you end up implementing in VBA
for them? Do you ever have the feeling that some of these things ought to be
doable in Excel without having to resort to VBA?

(I ask because I'm working on a product in this space.)

~~~
zrz
Mostly functions that could be achieved with Excel formulas, but would require
to add some more columns. As we are already struggling with file size, we
often go with VBA implementation. I'm talking about calculating things like
drawdowns in series, and other timeseries properties. We compared both
implementation (Formulas ad VBA) and most of the time, VBA is several times
faster, and reduces chances of application freeze; So we are not even
discussing the need for VBA in the team.

