Blog

Save Excel.Range to image file

Saving a Spreadsheet Range as a image file

The following code will save a spreadsheet range as a bitmap:

Option Explicit 
 
Private Type PicBmp 
    Size As Long 
    Type As Long 
        hBmp As Long 
        hPal As Long 
        Reserved As Long 
    End Type 
     
    Private Type Guid 
        Data1 As Long 
        Data2 As Integer 
        Data3 As Integer 
        Data4(0 To 7) As Byte 
    End Type 
     
    Private Const CF_BITMAP = 2 
    Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" _ 
    (PicDesc As PicBmp, RefIID As Guid, ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long 
    Private Declare Function GetClipboardData Lib "user32"  _ 
    (ByVal wFormat As Long) As Long 
    Private Declare Function CloseClipboard Lib "user32" () As Long 
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long 
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ 
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long 
     
    Sub SaveImage(rng As Range, strFileName As String) 
        Dim hwnd As Long 
        Dim hPtr As Long 
        hwnd = FindWindow("xlmain", Application.Caption) 
        rng.CopyPicture xlScreen, xlBitmap 
        OpenClipboard hwnd 
        hPtr = GetClipboardData(CF_BITMAP) 
        SavePicture CreateBitmapPicture(hPtr), strFileName 
        CloseClipboard 
    End Sub 
     
     
    Function CreateBitmapPicture(ByVal hBmp As Long) As IPicture 
        Dim lngR As Long, Pic As PicBmp, IPic As IPicture, IID_IDispatch As Guid 
         
        With IID_IDispatch 
            .Data1 = &H20400; 
            .Data4(0) = &HC0; 
            .Data4(7) = &H46; 
        End With 
         
        With Pic 
            .Size = Len(Pic) 
            .Type = 1 
            .hBmp = hBmp 
        End With 
         
        lngR = OleCreatePictureIndirect(Pic, IID_IDispatch, 1, IPic) 
        Set CreateBitmapPicture = IPic 
    End Function 
     

To use it pass the range you want to display and a filename to use e.g.

SaveImage Sheet1.Range("A1:A8"), "C:Documents and settingsmarkdesktoptest.bmp" 

Incidentally if you used VB6 and compiled to a COM addin you would only need:

SavePicture Clipboard.GetData(vbCFBitmap), "C:Documents and settingsmarkdesktoptest2.bmp"
If we want to save as JPG file
Sub SelectedRangeToImage()
    Dim tmpChart As Chart
    Dim n As Long
    Dim shCount As Long
    Dim sht As Worksheet
    Dim sh As Shape
    Dim fileSaveName As Variant
    Dim pic As Variant
    'Create temporary chart as canvas
    Set sht = Selection.Worksheet
    Selection.Copy
    sht.Pictures.Paste.Select
    Set sh = sht.Shapes(sht.Shapes.Count)
    Set tmpChart = Charts.Add
    tmpChart.ChartArea.Clear
    tmpChart.Name = "PicChart" & (Rnd() * 10000)
    Set tmpChart = tmpChart.Location(Where:=xlLocationAsObject, Name:=sht.Name)
    tmpChart.ChartArea.Width = sh.Width
    tmpChart.ChartArea.Height = sh.Height
    tmpChart.Parent.Border.LineStyle = 0
    'Paste range as image to chart
    sh.Copy
    tmpChart.ChartArea.Select
    tmpChart.Paste
    'Save chart image to file
    fileSaveName = Application.GetSaveAsFilename(fileFilter:="Image (*.jpg), *.jpg")
    If fileSaveName <> False Then
      tmpChart.Export Filename:=fileSaveName, FilterName:="jpg"
    End If
    'Clean up
    sht.Cells(1, 1).Activate
    sht.ChartObjects(sht.ChartObjects.Count).Delete
    sh.Delete
End Sub

VBA UserForms

Userforms

written by Helen Toomik – Last updated Oct 2004

Contents:

Caveats… Please note that this is not “Excel VBA 101” – I assume you are already familiar with VBA and in particular Excel. I developed this primarily for Excel 2000 and 2002/XP, and some parts are not applicable to Excel 97.

Thanks… to everybody who helped with proofreading, debugging and other suggestions.

Introduction – What is a userform?

A userform is a custom dialogue box that you can use when MsgBox and InputBox are just not enough. Userforms can be used for displaying as well as inputting data. You could even build an entire user interface using userforms, and never let the user touch the spreadsheet itself.

Most of the things that you can see in Windows dialogue boxes can be replicated in userforms – commandbuttons, option buttons, checkboxes, textboxes, comboboxes – plus a whole lot of other, more rarely used controls like graphs, calendars, etc.

This tutorial is an introduction to basic userform concepts, as well as commonly used userform techniques. I’ll explain some of the more general ideas “verbally”, but I’m also including a sample file where you can see how it all works in practice, so the tutorial text itself won’t have many code samples.

I’ve put this together mainly for Excel, but the principles (and most of the code) would also apply to other MS Office applications. The main difference would be in the parts that transfer data between userform and your document.

Getting started

  1. Create a form. Open the Visual Basic Editor (Tools…Macros… or Alt+F11). Add a userform by choosing Insert…Userform from the menu bar, or right-click in the project explorer and do the same thing from there.
  2. Add controls. When you create a userform, a new floating toolbox window will appear, called the Controls Toolbox. (If it doesn’t, you can find it in the View menu.) This shows the most common types of controls. Click on one that you like, and simply draw the control in your userform.
  3. More controls. Many more controls are available but not immediately visible. Right-click on the Controls Toolbox and choose Additional Controls. You can add calendars, spreadsheets, graphs, etc.
  4. Add event procedures. Events allow the form and its controls to respond to what the user does. Event procedures sit in the code module associated with the userform (its class module). You can access the code by double-clicking on the form/control, or right-clicking on the form/control and choosing “View Code”, or right-clicking on its icon in the project explorer. See the events below.
  5. Show the form. To show the userform, open the form’s class module and hit F5, like you’d do for running a macro, or click the Play button on the toolbar. Note that pressing F5 in a class module does not run the sub that your cursor is in, but the userform itself.
  6. Debug. As with macros, F8 allows you to step through the code line by line.

Forms toolbar controls vs. Control toolbox controls

If you have used controls in Excel, you may have used controls from the Forms toolbar, rather than the Controls toolbox. The two sometimes look very similar, but work quite differently behind the scenes. Ozgrid has a fairly good overview of the differences.

Finding out more

If you want to know more after reading this tutorial, there are three easy ways:

  1. The example file I’ve included. It has examples for the commands I’ve mentioned here, in particular all the FAQs, and some more.
  2. Help. The VBA help files for userform-related commands are fairly good. Take a look at their examples, too.
  3. Object browser (F2). Shows you what properties and methods are available for a certain object. Pressing F1 here shows the Help section for that object/method/property.

Properties

Forms and controls have properties (like size, location, etc). You can change these while setting up the form, and most of them can also be changed at runtime (via code). The values you choose while setting up the form will be default values, and runtime changes will only be valid while the form is loaded (see Life cycle of a form below).

The two most important properties of objects are .Name and .Value:

  • Name is useful for referring to the controls. You can refer to controls by their index number, but that yields code that is difficult to understand and debug, so using names is generally preferable.
  • Value is what you use to actually input or output data. Value means slightly different things for different controls – for optionbuttons and checkboxes it is True/False, for textboxes the text they contain, etc. Value can be used both for input and for output.

For example:

txtFirstName.Text = Range("A1").Value 
Range("B1").Value = optVegetarian.Value 
 

A good habit

Give your forms and controls descriptive and systematic names. You’ll often find it helpful to give each type of control a prefix: frm for forms (frmMain and frmDetails), opt for option buttons etc. This makes your code easier to read, and also allows you to use some techniques that would otherwise be more complicated (see Looping through controls below).

http://www.xoc.net/standards/rvbanc.asp has a lot more detail on naming variables and objects.

Referring to controls and forms, and Me

When referring to the controls of a userform from code in its class module, you can refer to them by name: txtFirstName.Value = “John”. For controls in other userforms, the name of the control is preceded by the name of the form (e.g. frmEmployees.lstDepartments).

The same approach is valid for public variables declared in the form’s class module – these behave essentially as properties of the form (e.g. frmInputs.AllDone). Note that you cannot declare a variable as Global in the class module of a userform.

Me is a shortcut for referring to the userform from its class module (e.g. Me.Height). For example, referring to TextBox1 in UserForm1:

 
Me.TextBox1.Text ="Example" 
 'in the userform's class module, or:
UserForm1.TextBox1.Text ="Example" 
 'in a different module

The life cycle of a userform – showing and closing, etc.

To show your form manually, press F5 in the form window or in its class module. If there is a Userform_Initialize procedure in the module (see Events), that will run first.

To show and hide your form via code (for example, you might want a commandbutton on frmIntro to hide frmIntro and show frmMain instead), use the .Show and .Hide methods.

Advanced topic: Load and unload

Behind the scenes, there’s more than just showing and hiding going on. Before a form is shown, it needs to be loaded into memory. If you show a form that hasn’t been loaded, it will load automatically.

Indeed, any reference to the form, or to a variable or control or property of the form, will force it to load, and therefore trigger the Initialize event.

If you want to initialise the form without showing it, you can load it like this:

Load frmMain 

After hiding a form, it will still be loaded. If you show it again, the Initialize procedure will not run again. (The Activate procedure will, however.) To clear the form from memory, you have to unload it. When the user closes a form with the close button, the form is automatically unloaded.

So the sequence is: Load – Show – … – Hide – Unload.

Unload clears all variables in the form’s module – it is comparable to stopping a procedure. Any values that the user has entered will be lost, and controls will revert to their default values that you have entered using the Properties window. If you want to save their values, you need to do so before unloading the form.

Modal vs modeless

Forms can be shown in one of two “modes” – modal or modeless. Modal forms do not allow the user to do anything else in Excel while the form is visible – like a MsgBox. Modeless forms allow the user to move around in Excel, do other things, and then return to the form.

Forms can be shown in one of two “modes” – modal or modeless. Modal forms do not allow the user to do anything else in Excel while the form is visible – like a MsgBox. Modeless forms allow the user to move around in Excel, do other things, and then return to the form.

Code execution will also continue in the background while a modeless form is shown. You can make your code wait until the form has been closed using a loop that checks whether the form has been closed:

 
Do Until frmOther.Visible = False 
    DoEvents 
Loop 

The default setting is modal (NB: the opposite of the case in VB6). Once a form is shown as modal, you cannot change it to modeless – you have to hide the form and then show it again, specifying that you want it modeless.

Modeless forms are only available from Excel2000 onwards.

Events

This is where things get interactive. Events allow the form and its controls to respond to what the user does. You are probably familiar with events from Excel VBA – Workbook_Open, Worksheet_Change etc. While you can do a lot in Excel without events, forms are pretty useless without them.

  • Common events for forms include Initialize, Activate, QueryClose, and Click.
  • Common events for controls include AfterUpdate, Change, Click, Enter and Exit.

To insert an event procedure, right-click on the object and choose “View code”. An event procedure is created automatically for that control’s standard event. To create a procedure for a different event, choose the event you want from the drop-down menu at the top right of the VBE window. Alternatively, go to the form’s class module and choose the object from the left drop-down and the event from the right drop-down.

Userform_Initialize

The most important event for forms is the Initialize event. Initialize is pretty much the first thing that happens to a form – the Initialize event is triggered as soon as the form starts loading, either because it is called by code or by the user (by hitting F5 or F8).

This is where you would initialise variables and controls. For example, you can update textboxes with latest values from the spreadsheet, change the default value of a textbox to today’s date, etc.

QueryClose and Terminate

The equivalent “end of life” events for a form are two: QueryClose and Terminate. QueryClose occurs first, and gives you the chance to cancel it (and not close the form); Terminate is final and not possible to cancel.

So the sequence of events is: Initialize – …- QueryClose – Terminate.

Userform_Activate

If you hide a form without unloading it, and then show it again, Initialize won’t run again. Instead, the Activate event occurs. Activate is triggered every time the form gets focus. This happens each time the form is shown. If you have several forms visible at the same time, the Activate event is also triggered every time you switch between forms.

Events with parameters

As with Excel events, some events have parameters, which give you more information about how and why the event was triggered – the UserForm_KeyDown event tells you which key was pressed, etc. When you create an event procedure for one of those events using the drop-down menus in VBE, the procedure is automatically set up to properly capture all the parameters.

Some parameters are read-only, whereas others are read/write. For example, the Userform_QueryClose event, which occurs before a form is closed, has a Cancel parameter. Setting Cancel = True within the procedure cancels the event, and the form is not closed.

FAQ: How can I pass data between userforms?

There are two main approaches to passing data between forms. Either you pass data directly from form to form (which means that the two forms are loaded in memory simultaneously) or you store the data somewhere and then access it later from the other form.

Passing from form to form can be done from either the “source” form or the “target” form. Remember that this has to be done before the source form is unloaded. Also remember that when referring to controls in another form, you need to specify the form name:

txtName.Value = frmTheOtherForm.txtName.Value 

Passing data directly from form to form will trigger the Initialize event of the other form (unless that form is already visible) since you are referencing its controls’ properties. If the Initialize event procedure in its turn includes code that shows the form, or calls other subs, this can easily trigger a lot of code, and get difficult to debug, so I’d use this approach with relatively simple forms only.

In comparison, storing the data in a variable allows more flexibility and control. The data could be stored either in a public variable, in a worksheet cell (so that it can be saved when the file is closed) or in a name in the worksheet.

The sample file has examples of both approaches.

FAQ: How can I do … with all the … in my form?

For example, how can I add up the values of all textboxes? uncheck all checkboxes? etc.

You can loop through all the controls in your form, pick out those that are of the right type, and apply the relevant code to those.

As with most things, there are several ways of doing this… using TypeName, TypeOf or control names.

TypeName()

TypeName returns a string – “TextBox” for textboxes, “CommandButton” for commandbuttons etc. The typename of a control is generally the same as the control’s default name & caption, but without the number. For example, when you add a new textbox to your form, it is called TextBox1 by default.

TypeName is case-sensitive, and if you make a typo, you get no error message – the code simply won’t work properly.

TypeOf

TypeOf is a fancier way of doing the same thing. Instead of a string, it returns the object type directly, referencing the object library. You can find the library and type of an object through the Object Browser. For example, the type of a textbox is MSForms.Textbox.

The main practical advantage of TypeOf is that it makes your code easier to debug. Typos get caught when you try to compile the module, and TypeOf supports Intellisense – start typing If TypeOf ctl Is, and you’ll get a dropdown list with all the available choices.

“If TypeOf … Is … Then” is considered a special case of If statements, and is covered in VBA help under If.

Control name

If you have named your controls consistently, you can use the Name property to identify them, with the help of the Left() function. This “low-tech” approach is somewhat more flexible – you could use this to identify a subset of all textboxes. If, for example, your form has 10 textboxes (txtTeamName, txtTeamNumber, txtMember1, txtMember2, …, txtMember10) you could use this approach to identify and empty all textboxes whose name starts with txtMember.

Examples

The three alternative If statements in this example would all achieve more or less the same thing (assuming the names of your textboxes all start with “txt”):

 
Dim ctl As Control 
For Each ctl In Me.Controls 
    If TypeOf ctl Is MSForms.Textbox Then 
         'or
         'If TypeName(ctl) ="TextBox" Then
         'or
         'If Left(ctl.Name, 3) ="txt" Then
         'do something with the textbox
        ctl.Text ="Hello" 
    End If 
Next ctl 
 

There’s an example in the sample file above, too.

FAQ: How can I get data into my listbox?

RowSource

You can link a listbox directly to a range in your worksheet. This is similar to having a listbox in the worksheet. Any changes you make to that range will immediately be reflected in the listbox. The link is read-only: you cannot change items in the listbox to change the worksheet.

Note that the parameter for RowSource is a string and not a range:

lstNames.RowSource = Range("Names").Address 

List / Column

You can set list items using the List and Column properties. The two are essentially the same, except for their orientation: the syntax is List(row, column) and Column(column, row). Both can be used to copy an entire array to the listbox, or to set individual items.

lstNames.List = Range("Names").Value 

AddItem

AddItem adds a single row to the listbox, and can put a value in the first column of the new row. For multicolumn listboxes, you’ll have to use List or Column to put in values in the rest of the columns.

With lstNames 
    .Additem 
    .List(0, 0) ="John" 
    .List(0, 1) ="Smith" 
End With 

You’ll find examples of all these three methods in the sample file.

FAQ: How can I create a progress bar for my loop?

One easy way to create a progress bar is to create a form with two labels (call them lblBackground and lblProgressBar for example). Make lblBackground as wide as you want the progress bar to be, and set the width of lblProgressBar equal to 0 to start with. Also make sure that lblProgressBar is in front of lblBackground. Then increase the width of lblBar as you run the loop.

Me.lblProgressBar.Width = p * Me.lblBackground.Width 
 'where p is the proportion of the loop that's done, for example 0.75

Again, there’s an example in the sample file.

VBA Code Compilers

First point to state is the VBA SDK 6.x site, on how to upgrade Visual Basic 6
to Visual Basic 6.5 with VBA’s SDK + VB.NET. Don’t know where it came from, but looks really insteresting. You can get your own VBA system on your application.
Then, there is this book (avaliable at the archive.org library), about hardcore of Visual Basic. It really exposes harcore for VB, like raw COM creation… and other weird things like that. I get to this following this post, and before that I was pursuing the Nativecode thing that is exposed there, from the Unviewable application. Also it seems that this also shares some kind of operation.
Orlando has always get me wondering how he get’s Excel to EXE compiled, and some other nice tools.
Want to get the most on Office VBA?, then borrow this book, avaliable via the archive.org library.
Following is a recopilation of what comercial software can do right now in Excel workbook protection tools, focused on the base features of the products which affect the security and misbehavior of protected solutions.
There are other decompilers for VBA:
  • To simply unlock the xls/xlsm file, just go here.
  • https://github.com/bontchev/pcodedmp

For Visual Basic programs compiled to .NET (e.g. with Visual Studio .NET 2003 or later), see DotNetDecompilers.

  • https://www.decalage.info/vba_tools and http://decalage.info/vba_emulation
  • JosephCo wrote a decompiler called Exdec.
  • VBDis 3/4/5 by DoDi. Version 3 (for VB version 3) was the most successful, because it has the most information in the “executable” (including comments!). VBDis 3 is available from this page.
  • Decompiler Technologies (formerly Visual Basic Right Back) is a decompilation service.
  • http://www.vb-decompiler.com: VB Decompiler Forum. Even includes some specifics on building a VB5/VB6 decompiler.
  • http://www.vb-decompiler.org: decompiles pcode, “high level assembler” for native code. Proprietary software, but free (as in beer) “Lite” version is available.
  • VBDE by Iorior dumps GUI information about VB executables; some decompilation ability
  • WKTVBDebugger VB debugger by WTK
  • http://www.decompiler-vb.netVBReFormer is a decompiler for native Visual Basic applications. It shows design code data (forms, controls, etc.), allows the modification of design properties directly on the binary, can disassemble native code, and can decompile native code (as much as that is possible).
  • Another one here, http://www.vbdecompiler.co.uk/
  • VBEditor by Hexman recovers Gui Information
  • R.A.C.E. by Sarge recovers Gui Information, and P-Code opcodes.
  • P32Dasm by Darker is a P-Code decompiler which decompiles to P-Code tokens.
  • Semi VB Decompiler by vbgamer45 recovers gui information, and P-Code tokens. For VB 5 and 6. Does not recompile native code.
  • VB Parser – Recovers P-Code tokens.
  • VBRezQ recovers the project file and forms.
  • http://vbdebug.cjb.net for a Visual Basic debugger (p-code programs only) by Mr Silver and Mr Snow.
  • VB Shrink by Cute-Bits is a tool that strips non essential info from the executable, to make it more difficult to decompile
  • VB EXE Obfuscator by Jory
  • Decompiler Defeater (see DeFeater)
  • Visual Basic MAK Compiler Pro (aka make_mak) by Christian Germelmann has a check box decompDefeat; the author claims “your apps will be immune against ‘reverse engeneering'”.
  • See also http://www.riis.com/archives/19980503.html on VB decompilation. (Dead link check out archive.org if you want to view that article.)
  • See also Master the Black Art of the VB Interpreter, Ash Rofail. In Visual Basic Programmer’s Journal, Dec 1996, pp 58-64.
  • The paper Visual Basic Reversed – A decompiling approach by Andrea Geddon has lots of details about the internals of VB5 and VB6 native (machine code) compiled programs. This is not a peer reviewed academic paper, and is written from a cracking point of view.
  • decompiler.com (bad link!) had some good links on this page, now only available from archive.orghttp://www.decompiler.com/viewtopic.php?t=2 (from the Software Reviews forum, Software Listings topic). There was also other good information in forums and the like; this site was heavily Visual Basic oriented. Several tools were available for download. Offline since late 2003.
From: #13 Re: Creating an .exe

Digiwise’s Post:

WhiteHatXL’s analysis:

DataSafeXL:
PROS: A newbie in Excel cannot reach the content of the protected workbook.
CONS: The product is programmed in vba. The tool relies on standard MS Excel protection methods like workbook/worksheet protection and vba project password plus the vba code obfuscating. You do not need to be C programmer to pass over such protection.
“For maximum VBA security, use the Microsoft Excel 2007-2010 format (“.xlsm”) and add a VBA password to your file which contains random alphanumeric characters and is at least 15 characters long.”
This advice from their help file just kills me!
Looks like the guy who wrote this product has been learning vba programming by the way.
SUMMARY: Junk. The product is on a student project level.Spreadsheet Sentry:
PROS: The tool is programmed in C++, uses strong encryption to protect formulas in cells. In protected solution formulas are encrypted, stored in cells and not readable.
CONS: Takes WAY too much time to encode a workbook even for middle size model.
Protected solutions work veeeery sloooow. Calculation time is wasted for encryption/decryption every formula in calculation chain.
The tool doesn’t protect VBA code.
SUMMARY: Protection works. The tool appropriates for small workbooks without VBA macros.LockXLS:
This is the most controversial product in the review. After the first glance I wanted to give “the perfect” mark, but see what I have finally found ….
PROS: The tool is programmed in C++. In protected solution everything works like in original workbook. Average user cannot see formulas in cells and vba code in protected workbook. Protects xla files. Lots of features.
CONS: The product uses unmodified, original workbooks in protected solutions and relies on hackers’ methods to take customer away from seeing the content of the workbook (Hooks Excel windows and blocks Excel’s COM objects methods calls). The tool is trying to stub all Excel’s security breaches, which is impossible with an unmodified, original workbook.
When a protected solution has been started on an end customer computer, the spreadsheet becomes as unprotected as newborn baby.
5 lines of C code extract the original workbook from the protected solution, doesn’t matter whether it’s xls or exe file.
To prove the vulnerability of LockXLS solutions, just e-mail a protected solution to WhiteHatXL at yahoo dot com , the original workbook will be returned.
SUMMARY: Fake. This product is not for a workbook protection.Secure Calc (former ExcelShield):
These guys have ignored my requests for a trial version, so I have evaluated the product by the simple example from their site—It may work different for complex spreadsheets.
PROS: The tool uses strong encryption for formulas, removes formulas from cells, has calculation engine to evaluate protected formulas.
CONS: Engine is written in .NET environment in pure managed code, which is not good for a protection tool. Source code may be reached by Reflector and reverse engineered. No VBA Protection.
The calc engine is not included into protected solution and should be preinstalled on customer computer separately. Each time, after typing new data into cell, the customer has to click the ‘Calculate’ button, which is available through the main Excel menu. This is very annoying.
SUMMARY: Protection works. It looks good enough, if you do not need to protect vba code.xCell Compiler:
PROS: The tool is programmed in C++, compiles formulas into binary code and removes them from cells, protects vba code. Has calculation engine to evaluate protected formulas. Has a lot of features.
CONS: The tool has limitations declared on their web site. A protected solution doesn’t support adding/removing columns/rows/sheets. Sorting for ranges with formulas doesn’t work.
SUMMARY: Protection works. Good, if you do not use features mentioned in limitations.

Excel Translator:
I was unable to get the neither trial version nor example of protected workbook. Very “strong” protection!

Converter XL to Exe:
PROS: Free, Free, Free!!!
CONS: This tool is not for a workbook protection.
SUMMARY: Toy.

Other interesting links to consider:

 

Call OCX COM sample

dilettante (MIS)
Start with a small script:

Jabber.vbs

CODE

Option Explicit

Function Jabber()
    Const ForReading = 1
    Dim FSO
    Dim strIn
    Dim tsIn
    Dim strOut
    Dim tsOut
    Dim strLine

    Set FSO = CreateObject("Scripting.FileSystemObject")
    strIn = InputBox("File to Jabber")
    If StrIn = "" Then
    Jabber = True
        Exit Function
    End If
    strOut = InputBox("New jabbered file")
    If strOut = "" Then
        Jabber = True
        Exit Function
    End If
    Set tsIn = FSO.OpenTextFile(strIn, ForReading)
    Set tsOut = FSO.CreateTextFile(strOut, True)
    Do Until tsIn.AtEndOfStream
        strLine = tsIn.ReadLine
        strLine = Replace(strLine, "a", vbFormFeed)
        strLine = Replace(strLine, "e", "a")
        strLine = Replace(strLine, "i", "e")
        strLine = Replace(strLine, "o", "i")
        strLine = Replace(strLine, "u", "o")
        strLine = Replace(strLine, vbFormFeed, "u")
        tsOut.WriteLine strLine
    Loop
    tsOut.Close
    Set tsOut = Nothing
    tsIn.Close
    Set tsIn = Nothing
    Set FSO = Nothing
    Jabber = False 'No cancels.
End Function

WScript.Echo "Start!"
If Jabber() Then
    WScript.Echo "Canceled!"
Else
    WScript.Echo "Done!"
End If

Then I started up VB5CCE and created an ActiveX control project.  I named the project Jabber and the UserControl Compiled.

Next I copied in the VBScript code for the function Jabber() into the UserControl’s code window.  Because VB5 did not have the Replace() function yet, I faced a decision.

One way would be to write my own Replace() using the string functions already in VB5.  The other is to write a function in the calling script that invokes VBScript’s Replace, and pass the calling script to the VB5 version of Jabber() as an Object, then invoke the function from the script as a method.

I chose the latter, and I called my Replace() wrapper function R().

Here is the VB5CCE code:

Compiled.ctl

CODE

Option Explicit

Public Function Jabber(ByVal Caller As Object)
    Const ForReading = 1
    Dim FSO
    Dim strIn
    Dim tsIn
    Dim strOut
    Dim tsOut
    Dim strLine

    Set FSO = CreateObject("Scripting.FileSystemObject")
    strIn = InputBox("File to Jabber")
    If strIn = "" Then
        Jabber = True
        Exit Function
    End If
    strOut = InputBox("New jabbered file")
    If strOut = "" Then
        Jabber = True
        Exit Function
    End If
    Set tsIn = FSO.OpenTextFile(strIn, ForReading)
    Set tsOut = FSO.CreateTextFile(strOut, True)
    Do Until tsIn.AtEndOfStream
        strLine = tsIn.ReadLine
        strLine = Caller.R(strLine, "a", vbFormFeed)
        strLine = Caller.R(strLine, "e", "a")
        strLine = Caller.R(strLine, "i", "e")
        strLine = Caller.R(strLine, "o", "i")
        strLine = Caller.R(strLine, "u", "o")
        strLine = Caller.R(strLine, vbFormFeed, "u")
        tsOut.WriteLine strLine
    Loop
    tsOut.Close
    Set tsOut = Nothing
    tsIn.Close
    Set tsIn = Nothing
    Set FSO = Nothing
    Jabber = False 'No cancels.
End Function

Not much has changed here from the original VBScript version.  I saved the project and compiled it, which registers the new Jabber.ocx on that machine in passing.

Then I slightly rewrote the calling script:

Jabber Compiled.vbs

CODE

Option Explicit

Dim Jabber

Function R(ByVal Expression, ByVal Find, ByVal ReplaceWith)
    R = Replace(Expression, Find, ReplaceWith)
End Function

WScript.Echo "Start!"

Set Jabber = CreateObject("Jabber.Compiled")
If Jabber.Jabber(Me) Then
    WScript.Echo "Canceled!"
Else
    WScript.Echo "Done!"
End If
Set Jabber = Nothing

The only deployment issue is making sure that the VB5 runtime is installed on target machines, and that Jabber.ocx gets copied and registered, such as by running:

regsrv32 {path}Jabber.ocx

This can be done from a command prompt or Start|Run dialog.  One could also create an OCX installer package, etc.

I think this is as close as you can get “for free” to compiling VBScript.  One might replace the calling script by using some free compiler for a language that supports ActiveX objects.  Then you’d have a 100% solution.

You can also easily use things like the Common Dialog control in VB5CCE to provide a nicer file dialog than you get by using Inputbox().  Adding a reference to the Microsoft Scripting Runtime lets you use early binding and defines the FSO constants, and for that matter you can use typed variables for better performance.

Create a COM Add-in

Introduction

COM addins were introduced in Office 2000. The alternative to a COM Addin is a standard addin such as an xla for Excel or Template for word. In essence COM addins are compiled dll’s (dynamic link libraries) that use the COM interface to interact with Office. The fact that they are compiled means that they:

  • are more secure
  • run faster
  • are able to use controls and structures unavailable in VBA

The drawback though is that they a bit harder to:

  • create
  • edit
  • install

There are basically few options to compile directly from VBA code:

  • Visual Basic (5 or 6), as Add-in (DLL or OCX)
  • Visual Basic (5 or 6), as a compiled EXE from XLS file
  • Visual Basic 5 Controls Creation Edition, as ActiveX Add-in (OCX)

All probably will require a little code edition, but should be minor editions.

Creating a COM Addin in VB6

[Adapted from Mark Rowlinson]

Open VB6 and create a new project. In the list of available projects select the type Addin.

When the new project opens you will have a Form called frmAddin and a Designer object called Connect. Remove the form from the project as it is a sample form and we don’t need it.

The next step is to set the addin up using the designer object. Double click on it and you will be presented with a Designer Form with several fields.

  • Addin Display Name – This is the name of your addin.
  • Addin Description – Give your addin a description so users know what it does
  • Application – The application you are creating the Addin for. For the purposes of this article we will create an Addin for Excel so select Excel.
  • Application Version – The version of the application you are creating it for.
  • Initial Load Behaviour – Whether the addin loads automatically when the application is started up or not. For this addin set this field to Startup. The addin will then load when Excel starts.

Setting References and Other Project properties

The next thing to do is to set the required references. By default VB adds a reference to the VB6 extensibility library i.e. it assumes we are creating an addin for VB6. We wish to remove this and replace it with the reference to Excel.

To do this goto Project/References, uncheck the VB Extensibility Library and then find the reference to the Microsoft Excel Object Model. Check the box next to it and click OK.

Next goto to Project/Properties (bottom of the Project menu) and set the name and description of the project. (The difference here between the name set in the designer is that as we are creating a standard COM/ActiveX dll it could be added to a reference to other projects as well as being used as an addin. The Description here is the one that will appear in the references list.) This would then be a good time to save the project.

More on the Designer

Right click on the designer object in the project explorer and select ‘View Code’ or click the ‘View Code’ button at the top of the project explorer.

You will notice there is already alot of code in here that was generated for the sample Addin. Most of this code can be removed from the module. The only 2 procedures you need to leave behind are:

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _ 
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant) 

And

Private Sub AddinInstance_OnDisconnection _ 
    (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant) 

These 2 procedures relate to the starting up and closing down of the addin. The starting up procedure passes a reference to the application that loaded the addin. In our case this will be an Excel application object. We can store this and use it for manipulating Excel from within the addin. Add a standard code module to the project and add the following line of code:

Public xlApp As Excel.Application

Now go back to the OnConnection procedure. Remove the existing code and add:

Set xlApp=Application

This procedure is also the place to initialise any objects and create any menus/toolbars etc. Therefore add a procedure CreateToolbarButtons to your code module and call it from you OnConnection procedure. The completed procedure should now look something like the following:

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _ 
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant) 
     
     'store the application
    Set xlApp=Application 
     
     'set up our menus
    CreateToolbarButtons 
     
End Sub

The OnDisconnection procedure is called when the addin is unloaded. It should therefore be used to remove any toolbars and destroy any objects etc. The procedure should therefore look something like:

Private Sub AddinInstance_OnDisconnection _ 
    (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant) 
     
     'destroy xl object reference
    Set xlApp=Nothing 
     
     'removemenus
    RemoveToolbarButtons 
     
End Sub

You should also now add a procedure to your code module called RemoveToolbarButtons.

This is the basic structure of a COM addin completed – not too hard really!

Handling Toolbar Events

In Excel VBA when you create a commandbar button you can set the OnAction property to the name of the procedure you wish to run when the button is clicked. With COM addins, although you can still do this, when the button is clicked the OnAction procedure will not be found if it resides within the dll. OnAction will only look in standard VBA code modules.

This means that to handle the events you have to use a class module and declare a reference to the object ‘WithEvents’. You can then access the click event of button (other controls have different events available). Therefore add a class module to your project and name it cbEvents. Now add the following line:

Public WithEvents cbBtn As CommandBarButton 

Now if you can select cbBtn in the top left dropdown and select the click event in the top right dropdown. The following code should result:

Private Sub cbBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) 
End Sub 

Notice you are passed a reference to the control that caused the procedure to be called. We shall use this class module to handle the click event of all the buttons we create so we need to check which control was passed to determine the procedure to call. The easiest way to do this is to check the OnAction property. We can set this when we create the buttons to the name of the procedure we wish to call and use a select case statement to call that procedure e.g.

Select Case Ctrl.OnAction 
Case "Sub1" 
    Sub1 
Case "Sub2" 
    Sub2 
End Select 

Finally, we need to set the CancelDefault property to true within this event to cancel Excel looking for the sub specified in the OnAction property which as discussed earlier it won’t find!

The completed class module should therefore look something like:

 
Option Explicit 
 
Public WithEvents cbBtn As CommandBarButton 
 
Private Sub cbBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) 
     
     'supress errors
    On Error Resume Next 
     
     'check onaction property
    Select Case Ctrl.OnAction 
    Case "Sub1" 
        Sub1 
    Case "Sub2" 
        Sub2 
    End Select 
     
     'cancel Excel looking for the sub specified in the OnAction property
    CancelDefault = True 
     
End Sub 

You should now create your toolbar buttons in the usual way in your CreateToolbarButtons procedure remembering to use xlApp instead of Application to reference the commandbars collection. The only difference is that when you have created the button you should assign it to an instance of the class we just created. The easiest way to do this is to declare an instance of the class and of a collection at the top of the module i.e.

Dim ButtonEvent As cbEvents 
Dim ButtonEvents As Collection 

Now at the start of your CreateToolbarButtons procedure create a new collection:

Set ButtonEvents=New Collection 

Finally after each button you create create a new instance of cbEvents class and assign to ButtonEvent, assign the created button to the cBtn of the ButtonEvent object then add the ButtonEvent object to the collection. In code this becomes (assuming your newly created button is in an object btNew):

Set ButtonEvent=New cbEvents 
Set ButtonEvent.cBtn=btNew 
ButtonEvents.Add ButtonEvent 

An example procedure is as follows:

Public Sub CreateToolbarButtons() 
     
     'to make sure the buttons we are about to add aren't added twice
     'try to remove them first
    RemoveToolbarButtons 
     
     'declare some variables
    Dim cbBar As Office.CommandBar 
    Dim btNew As Office.CommandBarButton 
     
     'create a new collection
    Set ButtonEvents=New Collection 
     
     'find the worksheet menu bar in excel (this is the one
     'with the file, edit, view etc. commands)
    Set cbBar = xlApp.CommandBars("Worksheet Menu Bar") 
     'add a new button to the Tools menu
    Set btNew = cbBar.Controls("Tools").Controls.Add(msoControlButton, , , , True) 
    With btNew 
        .OnAction = "Sub1" 
         'set a unique tag to make our custom controls easy
         'to find later to delete
        .Tag = "COMAddinTest" 
         'set the tooltip text
        .ToolTipText = "Calls Sub1" 
         'set the caption that appears in the menu
        .Caption = "Sub1" 
    End With 
     
     'get a new instance of our cbevents class
    Set ButtonEvent = New cbEvents 
     'now assign the button we created to it
    Set ButtonEvent.cbBtn = btNew 
    ButtonEvents.Add ButtonEvent 
     
     'now add another button
    Set btNew = cbBar.Controls("Tools").Controls.Add(msoControlButton, , , , True) 
    With btNew 
        .OnAction = "Sub2" 
         'set a unique tag to make our custom controls easy
         'to find later to delete
        .Tag = "COMAddinTest" 
         'set the tooltip text
        .ToolTipText = "Calls Sub2" 
         'set the caption that appears in the menu
        .Caption = "Sub2" 
    End With 
     
     'get a new instance of our cbevents class
    Set ButtonEvent = New cbEvents 
     'now assign the button we created to it
    Set ButtonEvent.cbBtn = btNew 
    ButtonEvents.Add ButtonEvent 
     
     
End Sub 

The RemoveToolbarButtons sub would then look something like:

Public Sub RemoveToolbarButtons() 
     
    Dim cbBar As CommandBar 
    Dim cbCtr As CommandBarControl 
     
     'supress errors - this is important here as they may not have been created
     'yet or may have been alreday deleted
    On Error Resume Next 
     'need to remove button from the command bar
     'first find the commandbar
    Set cbBar = xlApp.CommandBars("Worksheet Menu Bar") 
     'not find the control using the tag we set when creating
    Set cbCtr = cbBar.FindControl(, , "COMAddinTest") 
    While Not cbCtr Is Nothing 
         'now delete it
        cbCtr.Delete 
        Set cbCtr = cbBar.FindControl(, , "COMAddinTest") 
         
    Wend 
     'remove event handlers from memory
    Set ButtonEvents = Nothing 
    Set ButtonEvent=Nothing 
End Sub 

 

Installing an Excel COM Addin

Registering the Dll

Dll’s require registering with Windows in order to run. To do this you need to run regsvr32.exe passing it the filename of the dll. An easy way to do this is to download the script on the downloads page. Once you have run the script you’ll be able to Register and Unregister dll’s easily using the right click context menu.

NB: When you create the DLL in VB6 it will auto register it on the PC it was created on.

NB2: Also note that to remove or delete a dll you should unregister it first. You can again do this easily if you have ran my script but if not you need to run regsvr32.exe passing the /u switch.

Installing the Addin in Excel

As we set the initial load behaviour to ‘Startup’ in the designer the Addin should load automatically on reloading Excel. If startup isn’t selected then you may need to load it manually. To do this you need to select the ‘Com Addins’ menu option in Excel.

This is often not available by default and needs to be added by going to Tools/Customise and dragging it on to the menu bar. On selecting the ‘Com Addins’ button the available Com addins will be listed. The Addin should be in the list with a checkbox next to it. This will be checked if it is loaded and unchecked if unloaded. You can also remove addins and browse for other addins not in the list.

 

Create ActiveX controls using the VB 5 CCE

[adapted from http://www.borncity.com/web/WSHBazaar1/WSHVB5CCE.htm]

Visual Basic 5 Controls Creation Edition (VB 5 CCE)  (Note: Microsoft has removed VB 5 CCE in 2004, but could be found on Microsoft Press book about Access Basic, on the companion CD), is basically a stripped-down version of the full Visual Basic 5.0 application; i.e., it does not include the functionality to create DLL and EXE projects and it only has limited documentation.

Visual Basic Runtime msvbvm50.exe (1.3m)
Visual Basic 5.0 Control Creation Edition vb5ccein.exe (7.1m)
Visual Basic 5.0 CCE Help Files ccehelp.exe (2.5m)

Hereafter it explains the basic techniques to create an ActiveX control using VB 5 CCE. In addition, this page comes with a ready-to-use sample that you could use as a foundation to develope your own controls.

The steps to create an ActiveX control

Install VB 5 CCE in Windows, and fire it.

  1. After launching the program, a “New project” dialog asks you what to do. Select the ActiveX Control icon on the New property page and close the dialog window using the “OK” button. A new project will be created. This project contains one UserControl.
  2. Click in the project explorer window on the project name (“Project1(project1)”). Use the (Name) entry of the property page to re-name your project (from “project1” to “WSHTest” for instance).
  3. Click on the Entry “UserControl(UserControl)”. Use the (Name) entry in the property page to re-name your control from “usercontrol1” to “Test1” for instance.
  4. Save the project into a file (like WSHTest.vbp) using the Save button or the File menu.
  5. Double click on the UserControl icon to open the control window. Use the Create “View Code” button in the project explorer window to show the code page.
  6. Add the code for procedures and functions which will be used within the project. These entries are exposed later as the methods of your ActiveX control.
  7. Select the command “Make …..ocx” in the “Files” menu. If you used the file Test.vbp to save your project the name Test.ocx is used. I have used the name WSHTest.ocx in the ZIP-Archive.

In some environments, the last step opens some dialogs allowing you to set a few options for your ActiveX control. You may close these dialogs to set the default values. Then, an OCX-file with the name of your project file (or the selected name) gets created. The OCX control gets registered automatically on your machine. Within your WSH script you do not need to worry about this file name, because the Windows Registry keeps all the information where to find the control.

Note: The window of the project explorer may be found in the upper right corner of the picture shown above. Currently my WSHTest project contains only one UserControl module. The window in the lower right is the property window showing the properties of the selected module. Currently I have selected the UserControl Test1. The names shown in brackets within the project explorer window are derived from the file names. If you save the project the first time, a project file name is associated. I have used here Test.vbp, so all other files uses the same name but different extensions (like Test.ctl for the source code file of the control). I have named the OCX file as WSHTest.ocx (which is different than the project file name) to demonstrate that file names are not used within your WSH scripts.

Important: Thanks to Steven Bondi, who has tested my sample, I would like to add an additional note. I can’t publish here hundred pages of my WSH book used to explain the basics of ActiveX programming. But, my first release of this page was a bit to short. You may use my sample ZIP file to study the code. But take care that the path settings depend on my test machine (I run several Windows 98 builds on my test machine – therefore the project file contains path names that may not apply to all user machines). If you intend to create your own ActiveX control it is important to use a new project! This assures that all paths and libraries are set properly within the project. Also VB 5 CCE creates a unique GUID for your control. This GUID is requested to register your control as a COM object. If you would like to sample code from another project, use the export and import function of VB 5 CCE (the program can run only one project at a time – this is another limitation of VB).

Use the Control in your script

After completing these steps, your ActiveX control is ready to be used within VBA. You need the project name and the UserControl in the VB 5 CCE, required for the CreateObject (“TypLib.class”) statement. The following VBScript line creates a reference to your new ActiveX control:

Set objAdr = WScript.CreateObject("WSHTest.Test1")

The name WSHTest is the name of the type library and it corresponds to the project name. The subname Test1 is the class name of the object and it is the same as the UserControl name. After you create a reference to the object using the CreateObject method you may access the methods exposed from this object. The following statement uses the WSHDate method:

myDate = objAdr.WSHDate()

We use the object variable and the name of the method within an assignment statement. The result is assigned to the myDate variable. Pretty simple, isn’t it?

The source code for the sample ActiveX control

For your convenience I have provided a few simple methods within the UserControl Test1. The source code is shown below:

'************************************************
'* Project:  Test1 (file Test.vbp)
'* Author:   (c) Günter Born
'*
'* This control demonstrates how to use the VB 5 CCE to
'* create ActiveX-controls for the Windows Scripting Host.
'* Compile it using the "Make Test.ocx" command of the
'* "File" menu within the VB 5 CCE. The OCX component is
'* getting registered automatically.
'*
'* Use it at your own risk. In no event will the author be
'* responsible/liable for any consequences of the use of this software.
'* The control may be used for free. Distribution is allowed,
'* if the whole package is distributed with the copyright note
'* and a note pointing to my web-site. This demo was derived
'* from a real sample published in my book "Inside Windows
'* Scripting Host", Microsoft Press Germany. Further versions
'* of this control will be published on my WSH Bazaar at:
'* http://www.borncity.de
'*************************************************

Public Function WSHGetTime() As String
' Just to test, return current time
 WSHGetTime = Time
End Function

Public Function WSHGetDate() As String
' Just to test, return current date
 WSHGetDate = Date
End Function

Public Function WSHInputBox(Prompt As String, title As String, Default As Variant) As Variant
' For JScript: provide a InputBox-Function
 WSHInputBox = InputBox(Prompt, title, Default)
End Function

Public Sub WSHAbout()
 txt = "ActiveX-Control to extend the Windows Scripting Host" + vbCrLf + _
        "with a new Test object and and new methods. Get more from my" + vbCrLf + _
        "Windows Scripting Host Bazaar at:" + vbCrLf + vbCrLf + _
        "http://www.borncitye.de"
 MsgBox txt, vbOKOnly + vbInformation, "(c) Günter Borns WSH-Test - Version 1.1"
End Sub

'*** End

The ActiveX control exposes the methods WSHGetDateWSHGetTimeWSHInputBox and WSHAbout. You may call these methods from either VBScript or JScript. WSHGetDate and WSHGetTime returns the current date and the current time. The WSHAbout method invokes a simple dialog box with some information about the control. The WSHInputBox method invokes an Inputbox dialog. This will be rather helpful in JScript, because this language doesn’t support the InputBox function from VBScript. Having the right ActiveX control enables you to use WSHInputBox in JScript (without the trickier way I have shown in my sample pages).

The source code for a VBScript test program

To show you more about the way how to use the methods provided by the WSHTest.ocx, I have created a short VBScript program. (By the way, the interface of these methods are identically to the WSHExtendActiveX control offered for download from this site). Further details about using the ActiveX control may be obtained from the following listing.

'************************************************
' File:    TestOCX.vbs (WSH sample in VBScript) 
' Author:  (c) Günter Born
'
' Demonstrates how to access the Test.ocx component
' using a WSH script.
' Use at your own risk. No support, no liability.
'
' New samples are available at the the WSH Bazaar:
' http://www.borncity.de
'************************************************
Option Explicit

Dim objAdr 

MsgBox "Test whether the ActiveX control Test.ocx is registered." + _
        vbCRLF + "If a run-time error occurs, the OCX isn't registered." + _
        vbCRLF + "Then use the program RegisterOcx.bat."

' Create the Test object, requires Test.ocx, otherwise the
' following line creates a run-time error (can't create object).
Set objAdr = CreateObject("WSHTest.Test1")

' our first attempt to use some methods
WScript.Echo "Time:", objAdr.WSHGetTime()
WScript.Echo "Date:", objAdr.WSHGetDate()

WScript.Echo "Your Input was: ", objAdr.WSHInputBox ( _
        "Enter something", "WSHTest by G. Born", "Hello")

objAdr.WSHAbout
' *** End

After invoking the script (double click on the VBS file), you will be guided through a few dialogs showing the date and time, asking for input and showing the “About” dialog. As you can see, accessing the methods of the ActiveX control is very simple.

And here comes another simple sample which may be used in the real world: it shows how convenient the ActiveX control WSHTest.ocx may be for the JScript programmers. The following few lines of code implements an InputBox in JScript.

//************************************************
// File:    Input.js  (WSH sample in JScript) 
// Author:   (c) G. Born 
//
// This script demonstrates how to get a user input in
// JScript. I use the ActiveX control Test.ocx.
// Use AS IS without any warranty. The source:
// the WSH Bazaar at
// http://www.borncity.de
//************************************************
// 

var title = "InputBox function for JScript";
var prompt = "Please enter your name:";

// Create the new object
var WSHTest = WScript.CreateObject("WSHTest.Test1");

// Get the user input and display it with the Echo method
WScript.Echo (WSHTest.WSHInputBox (prompt, title, "Hello World"));
//*** End

After invoking the script (double click on the JS file), you will see an input box asking for your name. If you click on OK, the user input will be shown in a 2nd dialog box. Clicking Cancel returns an empty string which will also be shown in a message box.

The whole project may be downloaded as a ZIP file: ActiveXDemo.zip (8 KB). Unzip the content of your file into a separate folder. The folder will then contain the source code modules for VB 5 CCE, the ready to use OCX, the VBS/JS test files and two BAT files. If you have already installed VB 5 CCE you can register the OCX and use the VBS file to test the control. To register/unregister the control use the two BAT files RegisterOcx.bat and UnregisterOcx.bat. If you load the VBP file into VB 5 CCE, you can inspect the source code and you may compile the project into a new OCX file.

Some examples of how to use the control to control Excel or Word:

 

 

Compile XLS file to EXE

Extracted from https://www.mrexcel.com/forum/excel-questions/456284-how-create-exe-file-vba.html?highlight=resource:

In VB6, select Add-Ins, Add in manager, VB6 Resource editor, Loaded/Unloaded, OK, select VB resource editor on tool bar (green), click Add Custom resource (on tool bar of the Resource editor) and add your .xls file. Note the “CUSTOM” number added. Use the following code to instal the .xls file.

Code:
LoadDataIntoFile 120, "C:\Myfilename.xls"
' ***120 is Custom Resource number
' "C:\Myfilename.xls" is file destination

Public Sub LoadDataIntoFile(DataName As Integer, filename As String)
    Dim myArray() As Byte
    Dim myFile As Long
    If Dir(filename) = "" Then
        myArray = LoadResData(DataName, "CUSTOM")
        myFile = FreeFile
        Open filename For Binary Access Write As #myFile
        '
        Put #myFile, , myArray
        
        Close #myFile
    End If
End Sub

Indent code

Autoindenting PHP Code

originaly written by Mark Rowlinson

Introduction

PHP code uses {} symbols to group pieces of code such as branches and loops. This code makes use of that by checking for the existence of them in each line. If it find a { then there should be a positive indent of the next line. If it finds a } then there should be a negative indent of the current line.

How to use the code

The code requires a reference to the “Microsoft Scripting Runtime” in order to make use of the FileSystemObject to read in and write out the file. Currently it is also written for Excel in that is uses GetOpenFileName to obtain the file to parse, this could easily be modified for VB to use a common dialog.

How it works

The code uses 1 Sub and 2 helper functions and uses a global variable to store the current indent level. When it starts it sets the indent to 0 and opens the file. It then loops through each line in the file, parses it using the FormatPHPLine function and stores it in an array. It then loops through the array to write the lines back out to the file

The FormatPHPLine checks for the occurrence of { and } to detremine if a positive or negative indent is required. If a negative is found it is applied strightaway i.e. the global variable is decreased by 1. The required indent is then created using the indent function. Finally the global variable is increased by 1 if a positive indent was found.

Improvements

There is much scope for improvement, such as, allowing for multiple occurrences on one line or making sure only code not comments are searched which I may add in the future.

The Full Code

Dim intIndent As Long 
 
Sub FormatPHP() 
     
    Dim strFile As String 
    intIndent = 0 
    strFile = Application.GetOpenFilename("PHP Files (*.php),*.php") 
    If strFile = "False" Then Exit Sub 
    Dim fso As Scripting.FileSystemObject 
    Set fso = New Scripting.FileSystemObject 
    Dim ts As Scripting.TextStream 
    Dim strText() As String 
    Dim x As Integer 
    x = 0 
    Set ts = fso.OpenTextFile(strFile, ForReading, False) 
    While Not ts.AtEndOfStream 
        x = x + 1 
        ReDim Preserve strText(1 To x) 
        strText(x) = FormatPHPLine(ts.ReadLine) 
    Wend 
    ts.Close 
    Set ts = fso.OpenTextFile(strFile, ForWriting, False) 
    For i = 1 To x 
        ts.WriteLine strText(i) 
    Next i 
    ts.Close 
    MsgBox "Done!" 
End Sub 
 
Function FormatPHPLine(ByVal strPHP As String) As String 
     'check for { and } to determine indent
     'if { then positive indent
     'if } then negative
    Dim pos As Boolean, neg As Boolean 
    If InStr(1, strPHP, "{") > 0 Then 
        pos = True 
    End If 
    If InStr(1, strPHP, "}") > 0 Then 
        neg = True 
    End If 
     'apply negative indent
    If neg Then 
        intIndent = Application.WorksheetFunction.Max(0, intIndent - 1) 
    End If 
    strPHP = indent(strPHP) 
     'apply +ve indent
    If pos Then 
        intIndent = intIndent + 1 
    End If 
    FormatPHPLine = strPHP 
End Function 
 
Function indent(ByVal y As String) As String 
    y = Trim$(y) 
    If intIndent > 0 Then 
        For i = 1 To intIndent 
            indent = indent & vbTab 
        Next i 
        indent = indent & y 
    Else 
        indent = y 
    End If 
End Function 

Port VBA to… (JavaScript, C++, Python)

An ongoing project we have on mind is to have a kind-of VBA port-language software, with the ultimately goal to get VBA code compiled, if this is anyway possible, to COM or DLL so code will be effectively hidden from prying eyes.

There are some steps that have to be achieved in this process before getting the port:

  1. Recognize special or reserved words
  2. Set reserved words equivalences to other programming language
  3. Detect procedures (Sub/Functions) and its variables
  4. Detect objects (Type)
  5. Detect enumeration variables (Enum)
  6. Structure code in blocks (DoLoop, ForNext, IfThen…) that can be indepedent one from each other
  7. Indent code (for better readability and comprenhension)
  8. Port from VBA to new language
  9. Revise code for unhandled exceptions

From here on, there is a high chance to get code compiled and reuse over Excel through VBA without exposing original code.

So, other popular scripting languages, aside VBA, are Python, JavaScript and PHP, all of them had been already wrapped to run under Excel. Other languages considered, as they already have their own compiler, are C++, the collection on .Net platform, and Fortran, so their compiled DLLs can be used with Excel.

If language port can be done to any one of those, then, it can be achived for them all.


VBA to JavaScript converter

My main interest now is focused in learning JS, and get the port from VBA to JavaScript.

One site that game me some hints on how to start on the JS world came from here. The converter scripts are far from running, but the map to follow the conversions is compact and, somehow, clear.

There are some functions in VBA but not in JS, so here comes very handy the collecting work done in this CodeProject article by .

Are these sites worth looking?, seems yes

  • http://jsil.org/
  • https://www.codeproject.com/Articles/25069/JSBasic-A-BASIC-to-JavaScript-Compiler
  • http://jsc.sourceforge.net/

I started with the IF (elseif, else) block, then jumped to the FOR (for each) block, and ended with the LOOP (Do-While/Until, While-Wend) block.

Let’s see the code:

Public Sub sV2J_If()
    Dim CodeBlock As String

    'CodeBlock = "If Condition1 = 1 Then If Condition2 = 2 Then If Condition3 = 3 Then Action: If Condition4 = 3 Then Action" & vbLf & _
                "If Condition1 = 1 Then" & vbLf & _
                vbTab & "If Sub1 = 1 Then" & vbLf & _
                vbTab & "   sub1 = 1.1" & vbLf & _
                vbTab & "ElseIf Sub2 = 2 Then" & vbLf & _
                vbTab & "   sub2 = 1.2" & vbLf & _
                vbTab & "ElseIf Sub3 = 3 Then" & vbLf & _
                vbTab & "   sub3 = 1.2" & vbLf & _
                vbTab & "Else" & vbLf & _
                vbTab & "   sub4 = 1.4" & vbLf & _
                vbTab & "End If" & vbLf & _
                "ElseIf Condition2 = 2 Then" & vbLf & _
                "   Statement2 = 2" & vbLf & _
                "ElseIf Condition3 = 3 Then" & vbLf & _
                "   Statement3 = 2" & vbLf & _
                "Else" & vbLf & _
                "   Statement4 = 2" & vbLf & _
                "End If"

    Call fVbCleaner(CodeBlock)

    'CodeBlock = fIf(CodeBlock:=CodeBlock, _
                    Then»:=" Then", _
                    If»:="if ", _
                    ElseIf»:="elseif ", _
                    Else»:="else", _
                    EndIf»:="end if", _
                    Then«:=" {", _
                    If«:="if ", _
                    ElseIf«:="elseif ", _
                    Else«:="else", _
                    EndIf«:="}", _
                    Opener«:=" {", _
                    Closer«:="} ", _
                    Indenter:=" ", _
                    Comment:="'")

    CodeBlock = "For lgCounter1 = LBound(aStatement) To UBound(aStatement)" & vbLf & _
                "   Statement = lgCounter1" & vbLf & _
                "   For lgCounter2 = LBound(aStatement) To UBound(aStatement)" & vbLf & _
                "      Statement = lgCounter2" & vbLf & _
                "      For lgCounter3 = LBound(aStatement) To UBound(aStatement)" & vbLf & _
                "         Statement = lgCounter3" & vbLf & _
                "      Next lgCounter3" & _
                "   Next lgCounter2" & _
                "Next lgCounter1"

    CodeBlock = fFor(CodeBlock:=CodeBlock, _
                     For»:="For ", _
                     Next»:="Next", _
                     Break»:="Exit For", _
                     For«:="for ", _
                     Next«:="continue", _
                     Break«:="continue", _
                     Opener«:=" {", _
                     Closer«:="} ", _
                     Indenter:=vbTab, _
                     Comment:="'")
Stop
    Debug.Print CodeBlock
End Sub

Public Function fJSCleaner(ByRef CodeBlock As String) As Boolean
'!!! ToDo: only if not inside comment block
    Dim aComment() As String
    Dim aStatement() As String
    Dim lgStatement As Long

    ' Clean code
    CodeBlock = VBA.Replace$(CodeBlock, vbTab, "  ")        ' Avoid odd chars
    CodeBlock = VBA.Replace$(CodeBlock, vbLf, vbCrLf)       ' Avoid impropper breaks
'!!!!
    CodeBlock = VBA.Replace$(CodeBlock, "; ", vbCrLf)       ' Avoid combined lines
'!!!!

    ' Avoid traps
    CodeBlock = VBA.Replace$(CodeBlock, "if(", "if (")
    CodeBlock = VBA.Replace$(CodeBlock, "elseif(", "elseif (")
    CodeBlock = VBA.Replace$(CodeBlock, "for(", "for (")

'!!!!
    ' Break one line If
'!!!!

    CodeBlock = VBA.Join(aStatement(), vbCrLf)
End Function

Public Function fVbCleaner(ByRef CodeBlock As String) As Boolean
'!!! ToDo: only if not inside comment block
    Dim aComment() As String
    Dim aStatement() As String
    Dim lgStatement As Long

    ' Clean code
    CodeBlock = VBA.Replace$(CodeBlock, vbTab, "  ")        ' Avoid odd chars
    CodeBlock = VBA.Replace$(CodeBlock, vbLf, vbCrLf)       ' Avoid impropper breaks
    CodeBlock = VBA.Replace$(CodeBlock, ": ", vbCrLf)       ' Avoid combined lines
    CodeBlock = VBA.Replace$(CodeBlock, " _" & vbCrLf, " ") ' Avoid line continuation

    ' Break one line If
    aStatement() = VBA.Split(CodeBlock, vbCrLf)
    For lgStatement = LBound(aStatement) To UBound(aStatement)
        Do While VBA.InStr(1, VBA.LCase$(aStatement(lgStatement)), " then ", vbTextCompare) > 0
            aStatement(lgStatement) = VBA.Replace$(aStatement(lgStatement), " then ", " Then" & vbCrLf, 1, 1, vbTextCompare) & vbCrLf & "End If"
        Loop
    Next lgStatement

    CodeBlock = VBA.Join(aStatement(), vbCrLf)
End Function

Public Function fIIf(ByVal CodeBlock As String)
'ternary Operator (cond ? truepart : falsepart)

End Function

Public Function fForEach(ByVal CodeBlock As String, _
                     Optional ByVal For» As String = "For ", _
                     Optional ByVal Break» As String = "Break", _
                     Optional ByVal Continue» As String = "Exit For", _
                     Optional ByVal For« As String = "for ", _
                     Optional ByVal Break« As String = "break", _
                     Optional ByVal Continue« As String = "continue", _
                     Optional ByVal Opener« As String = " {", _
                     Optional ByVal Closer« As String = "} ", _
                     Optional ByVal Indenter As String = vbTab, _
                     Optional ByVal Comment As String = "'") As String
'for (x in person) {
'}
End Function

Public Function fFor(ByVal CodeBlock As String, _
                     Optional ByVal For» As String = "For ", _
                     Optional ByVal Next» As String = "Next", _
                     Optional ByVal Break» As String = "Exit For", _
                     Optional ByVal For« As String = "for ", _
                     Optional ByVal Next« As String = "continue", _
                     Optional ByVal Break« As String = "continue", _
                     Optional ByVal Opener« As String = " {", _
                     Optional ByVal Closer« As String = "} ", _
                     Optional ByVal Indenter As String = vbTab, _
                     Optional ByVal Comment As String = "'") As String
    Dim aForStatement() As String
    Dim aStatement() As String
    Dim lgStatement As Long
    Dim lgStatements As Long
    Dim bNewVar As Boolean
    Dim bGotoNext As Boolean
    Dim Counter As String

    aForStatement() = VBA.Split(CodeBlock, ";")
    lgStatements = UBound(aForStatement) - LBound(aForStatement) + 1

    If lgStatements >= LBound(aForStatement) + 0 Then
        If VBA.Trim$(aForStatement(LBound(aForStatement) + 0)) = vbNullString Then
            'Optional
            Counter = aForStatement(LBound(aForStatement) + 1)
        Else
            If VBA.InStr(1, aForStatement(LBound(aForStatement) + 0), "var ") > 0 Then
                bNewVar = True
                Counter = aForStatement(LBound(aForStatement) + 0)
            Else
            End If
        End If

    ElseIf lgStatements >= LBound(aForStatement) + 1 Then
        If VBA.Trim$(aForStatement(LBound(aForStatement) + 1)) = vbNullString Then
            'Optional
        End If

    ElseIf lgStatements >= LBound(aForStatement) + 2 Then
        If VBA.Trim$(aForStatement(LBound(aForStatement) + 2)) = vbNullString Then
            'Optional
        End If
    End If

' for (i = 0; i < cars.length; i++) {
'}
'for(var x=0, x<n; x++){...}

End Function

Public Function fFor_(ByVal CodeBlock As String, _
                      Optional ByVal For» As String = "For ", _
                      Optional ByVal Break» As String = "Break", _
                      Optional ByVal Continue» As String = "Exit For", _
                      Optional ByVal For« As String = "for ", _
                      Optional ByVal Break« As String = "break", _
                      Optional ByVal Continue« As String = "continue", _
                      Optional ByVal Opener« As String = " {", _
                      Optional ByVal Closer« As String = "} ", _
                      Optional ByVal Indenter As String = vbTab, _
                      Optional ByVal Comment As String = "'") As String
End Function

Public Function fIf(ByVal CodeBlock As String, _
                    Optional ByVal Then» As String = " Then", _
                    Optional ByVal If» As String = "If ", _
                    Optional ByVal ElseIf» As String = "ElseIf ", _
                    Optional ByVal Else» As String = "Else", _
                    Optional ByVal EndIf» As String = "End If", _
                    Optional ByVal Then« As String = " {", _
                    Optional ByVal If« As String = "if ", _
                    Optional ByVal ElseIf« As String = "elseif ", _
                    Optional ByVal Else« As String = "else", _
                    Optional ByVal EndIf« As String = "}", _
                    Optional ByVal Opener« As String = " {", _
                    Optional ByVal Closer« As String = "} ", _
                    Optional ByVal Indenter As String = vbTab, _
                    Optional ByVal Comment As String = "'") As String
' Translate IF block

    Dim aStatement() As String
    Dim aStatementLevel() As Long
    Dim aStack() As Long
    Dim lgStatement As Long
    Dim lgStatement_End As Long
    Dim lgLevel As Long:    lgLevel = 0
    Dim strCode As String:  strCode = vbNullString
    Dim CodeOut As String:  CodeOut = vbNullString

    ' Split If sub-blocks
    CodeBlock = VBA.Replace$(CodeBlock, Then» & vbCrLf, vbCrLf)
    CodeBlock = VBA.Replace$(CodeBlock, Then», vbCrLf)
    aStatement() = VBA.Split(CodeBlock, vbCrLf)
    ReDim aStatementLevel(LBound(aStatement) To UBound(aStatement))
    ReDim aStack(LBound(aStatement) To UBound(aStatement))
    For lgStatement = LBound(aStatement) To UBound(aStatement)
        strCode = VBA.Trim$(VBA.LCase$(aStatement(lgStatement)))
        If strCode Like If» & "*" Then
            aStack(lgStatement) = 1
            aStatement(lgStatement) = VBA.Mid$(aStatement(lgStatement), _
                                               VBA.InStr(1, aStatement(lgStatement), If», vbTextCompare) + VBA.Len(If»))

            ' Avoid "=" comparison and apply "==" (Note: will fail for "===" intention!)
            aStatement(lgStatement) = VBA.Replace$(aStatement(lgStatement), "=", "==")

            lgLevel = lgLevel + 1
            aStatementLevel(lgStatement) = lgLevel

        ElseIf strCode Like EndIf» & "*" Then
            aStack(lgStatement) = 0
            aStatement(lgStatement) = "'" 'comment... avoid line
            aStatementLevel(lgStatement) = lgLevel
            lgLevel = lgLevel - 1

        ElseIf strCode Like ElseIf» & " *" Then
            aStack(lgStatement) = 2
            aStatement(lgStatement) = VBA.Mid$(aStatement(lgStatement), _
                                               VBA.InStr(1, aStatement(lgStatement), ElseIf», vbTextCompare) + VBA.Len(ElseIf»))
            aStatementLevel(lgStatement) = lgLevel

        ElseIf strCode Like Else» & "*" Then
            aStack(lgStatement) = 3
            aStatement(lgStatement) = "'" 'comment... avoid line
            aStatementLevel(lgStatement) = lgLevel

        Else ' action code
            aStack(lgStatement) = 4
            aStatementLevel(lgStatement) = -lgLevel
            aStatement(lgStatement) = VBA.Trim$(aStatement(lgStatement))
        End If
    Next lgStatement

    lgStatement = LBound(aStatement)
    lgStatement_End = UBound(aStatement)
    CodeOut = fIf_(aStatement(), _
                   aStatementLevel(), _
                   aStack(), _
                   lgStatement, _
                   lgStatement_End, _
                   Then»:=Then», _
                   If»:=If», _
                   ElseIf»:=ElseIf», _
                   Else»:=Else», _
                   EndIf»:=EndIf», _
                   Then«:=Then«, _
                   If«:=If«, _
                   ElseIf«:=ElseIf«, _
                   Else«:=Else«, _
                   EndIf«:=EndIf«, _
                   Opener«:=Opener«, _
                   Closer«:=Closer«, _
                   Indenter:=Indenter, _
                   Comment:=Comment)

    fIf = CodeOut
End Function

Public Function fIf_(ByRef aStatement() As String, _
                     ByRef aStatementLevel() As Long, _
                     ByRef aStack() As Long, _
                     ByRef lgStatement_Start As Long, _
                     ByVal lgStatement_End As Long, _
                     Optional ByVal Then» As String = " Then", _
                     Optional ByVal If» As String = "If ", _
                     Optional ByVal ElseIf» As String = "ElseIf ", _
                     Optional ByVal Else» As String = "Else", _
                     Optional ByVal EndIf» As String = "End If", _
                     Optional ByVal Then« As String = " {", _
                     Optional ByVal If« As String = "if ", _
                     Optional ByVal ElseIf« As String = "elseif", _
                     Optional ByVal Else« As String = "else", _
                     Optional ByVal EndIf« As String = "}", _
                     Optional ByVal Opener« As String = " {", _
                     Optional ByVal Closer« As String = "} ", _
                     Optional ByVal Indenter As String = vbTab, _
                     Optional ByVal Comment As String = "'") As String
' Translate IF sub-block

    Dim CodeOut As String
    Dim lgStatement As Long
    Dim lgStatement_Block As Long
    Dim lgLevel As Long
    Dim Indent As String
    Dim Indent_ As String

    lgLevel = aStatementLevel(lgStatement_Start)
    Indent = VBA.String(lgLevel - 1, Indenter)
    Indent_ = VBA.String(lgLevel, Indenter)
    CodeOut = vbNullString
    CodeOut = CodeOut & Indent & If« & "(" & aStatement(lgStatement_Start) & ")" & Then« & vbLf

    For lgStatement = (lgStatement_Start + 1) To lgStatement_End
        If aStack(lgStatement) = 1 Then ' if sub-block
            lgStatement_Block = lgStatement
            Do Until (aStack(lgStatement_Block) = 0) And (lgLevel + 1 = aStatementLevel(lgStatement_Block))
                lgStatement_Block = lgStatement_Block + 1
            Loop

            CodeOut = CodeOut & _
                      fIf_(aStatement(), _
                           aStatementLevel(), _
                           aStack(), _
                           lgStatement, _
                           lgStatement_Block, _
                           Then»:=Then», _
                           If»:=If», _
                           ElseIf»:=ElseIf», _
                           Else»:=Else», _
                           EndIf»:=EndIf», _
                           Then«:=Then«, _
                           If«:=If«, _
                           ElseIf«:=ElseIf«, _
                           Else«:=Else«, _
                           EndIf«:=EndIf«, _
                           Opener«:=Opener«, _
                           Closer«:=Closer«, _
                           Indenter:=Indenter)

        ElseIf aStack(lgStatement) = 2 Then 'ElseIf
            CodeOut = CodeOut & Indent & Closer« & " " & ElseIf« & "(" & aStatement(lgStatement) & ") " & Opener« & vbLf
            Do While (aStack(lgStatement) = 0) And (lgLevel = aStatementLevel(lgStatement))
                CodeOut = CodeOut & VBA.String(lgLevel, Indenter) & aStatement(lgStatement) & vbLf
            Loop

        ElseIf aStack(lgStatement) = 3 Then 'Else
            CodeOut = CodeOut & Indent & Closer« & " " & Else« & Opener« & vbLf

        ElseIf aStack(lgStatement) = 0 Then 'EndIf
            CodeOut = CodeOut & Indent & EndIf« & ";" & vbLf

        Else 'ElseIf aStack(lgStatement) = 4 Then 'And aStatementLevel(lgStatement) < 0
            If aStatement(lgStatement)  Comment Then
                CodeOut = CodeOut & Indent_ & vbTab & aStatement(lgStatement) & ";" & vbLf
            End If
        End If
    Next lgStatement

    lgStatement_Start = lgStatement_End
    fIf_ = CodeOut
End Function

Here is a VBA equivalent to JS Push function

Sub sPush()
Dim aArray() As Variant
Dim PushVal As Variant

aArray() = Range("$A$1:$B$5").Value2 'fArray2D(Array(1, 2))
PushVal = Range("$A$6:$B$8").Value2 'fArray2D(Array(3, 4))
aArray() = fArray2D(Array(1, 2))
PushVal = fArray2D(Array(3, 4))
Stop
fPush aArray(), PushVal
Stop
End Sub

Public Function fArray2D(ByRef aArray As Variant) As Variant()
Dim aArrTmp() As Variant
Dim lgArrayDim1 As Long
Dim lgArrayDim2 As Long
Dim lgR As Long
Dim lgC As Long

On Error GoTo Array2D
lgArrayDim1 = UBound(aArray, 1) - LBound(aArray, 1) + 1
lgArrayDim2 = UBound(aArray, 2) - LBound(aArray, 2) + 1

GoTo ExitProc

Array2D:
If lgArrayDim2 = 0 Then
ReDim aArrTmp(LBound(aArray, 1), _
LBound(aArray, 1) To UBound(aArray, 1))

For lgC = LBound(aArray, 1) To UBound(aArray, 1)
aArrTmp(LBound(aArray, 1), lgC) = aArray(lgC)
Next lgC

fArray2D = aArrTmp()
Erase aArrTmp()
End If

ExitProc:
On Error GoTo 0
End Function

Public Function fPush(ByRef aArray() As Variant, _
ByRef PushVal As Variant)
Dim aArrTmp() As Variant
Dim lgArrayDim1 As Long
Dim lgArrayDim2 As Long
Dim lgPushDim1 As Long
Dim lgPushDim2 As Long
Dim lgR As Long
Dim lgR_Push As Long
Dim lgC As Long

On Error GoTo ErrControl 'Resume Next
lgArrayDim1 = UBound(aArray, 1) - LBound(aArray, 1) + 1
lgArrayDim2 = UBound(aArray, 2) - LBound(aArray, 2) + 1
lgPushDim1 = UBound(PushVal, 1) - LBound(PushVal, 1) + 1
lgPushDim2 = UBound(PushVal, 2) - LBound(PushVal, 2) + 1

If lgArrayDim2 = lgPushDim2 Then
ReDim aArrTmp(LBound(aArray, 1) To UBound(aArray, 1) + 1 + UBound(PushVal, 1) - LBound(PushVal, 1), _
LBound(aArray, 2) To UBound(aArray, 2))
lgR_Push = -1
For lgR = LBound(aArrTmp, 1) To lgArrayDim1 - (1 - LBound(aArray, 1))
lgR_Push = lgR_Push + 1
For lgC = LBound(aArray, 2) To UBound(aArray, 2)
aArrTmp(lgR, lgC) = aArray(LBound(aArray, 1) + lgR_Push, lgC)
Next lgC
Next lgR

lgR_Push = -1
For lgR = lgArrayDim1 + LBound(aArray, 1) To UBound(aArrTmp, 1)
lgR_Push = lgR_Push + 1
For lgC = LBound(PushVal, 2) To UBound(PushVal, 2)
aArrTmp(lgR, lgC) = PushVal(LBound(PushVal, 1) + lgR_Push, lgC)
Next lgC
Next lgR
aArray() = aArrTmp()
Erase aArrTmp()
End If

ExitProc:
On Error GoTo 0
Exit Function
ErrControl:
GoTo ExitProc
End Function
[/sourcecode]