VBA to VB (Or any significantly useful language to process massive data)

Souleh · 10 May 2011 at 17:07

Hi all

Anyone here ever use VB (note NOT VBA) to edit excel sheets etc?

Ive built a number of scripts in VBA (Learnt VBA along the way...) and turns out I can make a SAP Export go from pure garbage into useful info in 4 minutes instead of 8-16hours.......

Only problem with VBA (As seen in my password minder sheet) is there is no code protection and the IP is free for the first person to unlock the editor..

Anyone know if its possible to get VB (Im assuming it has better security strength against being de-compiled) from my VBA with the same level of functionality etc? I would imagine you could import the VBA library into the main body of VB to allow this so please correct me if im wrong

Failing that.. has anyone managed to make spreadsheet code using any other languages?

Im talking between 30,000 and 1,000,000 rows of data along ~25 Columns

Many thanks (Im googling too as we speak...)

Dj_Jestar · 10 May 2011 at 17:19

It's not a fun thing.

You can "connect" to Excel using ODBC via ADO. Makes it a little less of a headache, but not by much.

I'd seriously consider importing that data to some form of DB tbh. A million rows of data is a bit much for a spreadsheet. And don't you dare think I mean Access

sparkymark75 · 10 May 2011 at 21:16

Try using EPPlus. It's a .NET library to read and create Excel sheets. I've used it in a few projects, although you'll need to use it in a .NET app.

csmager · 10 May 2011 at 21:49

If you want to use all the same functionality available in VBA from VB.NET or C#, you'll want to be using the Primary Interop Assemblies (add a reference to Microsoft.Office.Interop.Excel).

Note that any .NET language compiled to IL can fairly easily be reverse engineered unless you use Dotfuscator or similar.

FredFlint · 11 May 2011 at 01:14

You can use automation as MS Apps use COM. This can be used from any language. I used it a few years ago.

AHarvey · 11 May 2011 at 01:20

Something completely new, but a lad a work is using Python for some of our spreadsheets instead of vba?

Souleh · 11 May 2011 at 09:42

Many thanks guys for the starters - Seems its been certainly done before!

Very interested in hearing if the Python method is a go'er

Dj_Jestar · 11 May 2011 at 10:38

mparter said:
Try using EPPlus. It's a .NET library to read and create Excel sheets. I've used it in a few projects, although you'll need to use it in a .NET app.

We've used that before, and recently-ish switched to using NPOI (aka HSSF). Both are still quite horrible, but way better than using Excel directly!

OspreyO · 13 May 2011 at 15:51

For sure you can use VBA in VB, and you can bundle up the VBA code in COM DLL. Theres also many 3rd party tools for encrypting passwords, or scrambling the VBA code. I've used some financial addons which use the DLL route. I dunno how secure they are, but they'd definately stop the average power user from messing with it. If that the aim.

I've used it myself, not to protect the code but to access some VB controls that are not in VBA.

But at others have said this would seem to be something that requires a database solution rather than using Excel, which is likely to be very slow. If as I suspect your simply creating reports from SAP data theres probably a better way of doing this. You can create Excel documents from XML is one method we use.

We'd probably do what your asking MS SQL , SSIA, XML and Excel. But you might not have those available. If speed is a problem, throwing the bulk of the work to the server, and MS SQL where it uses multi threading and can be vastly faster for processing data and queries on it. Just a thought.

Maybe I'm over thinking it though.

Souleh · 13 May 2011 at 19:11

Speed isnt a problem, im able to create a report of 200 lines long to summarise 500,000 rows within 40 seconds using VBA - This is across about 10 columns.

I use key indexing to store data locations in the original 500k sheet and can build a summary report in Word for specific exception cases (typically about 30)
This only takes a minute at most (That most time being taken by word saving the document)

What you mention though sounds exactly what im after - Intellectual property rights protection from the average user.

Will look into the dll route - take it then you just distribute the DLL and the compiled VB code ? Sounds ideal