Table of Contents

Get StartedBack to TOC

To start running the high-level program, call the ExcelBricks.sRunProgram subroutine. This is usually done by putting it in the onclick event of a button (like in the template).

 Private Sub CommandButton1_Click()
  Call ExcelBricks.sRunProgram("Configuration1", "")
 End Sub

The subroutine can be called multiple times, with different parameter values and from different events (the onclick events of 2 separate buttons, for instance).

The subroutine takes 2 parameters

  1. The name of the configuration sheet - commands start from the 2nd row
  2. (can be blank) Database, user, password sets.

The 2nd parameter is used only if there is an SQL Server !Extract command. It's a ; separated list of database-user-password sets. Something like

 "Server1.DB1:User1:Password1;Server2.*:User2:Password2;*.DB3:User3:Password3"

When ExcelBricks sees an !Extract command, it checks if the server and database (Input 2 and Input 3) is in this list. If it is, it uses the listed user and password to connect to that database. Otherwise, the user is prompted at run time. The * wildcard is supported and comparison stops at the first match. If the user is set to . ExcelBricks uses Windows Authentication and credentials of the currently logged in user.

LegendBack to TOC

 A command
 A mandatory parameter for the command or sub-command
 An optional parameter
 What we are talking about will be in like this

General NotesBack to TOC

All command are case insensitive.

In case of any error the program exits with a message indicating the step tha failed. Prior to displaying this message the run log is sdisplayed. The error-handler closes all open database connections and cleans all objects in the core module. Memory cleanup and disposal of connections in the external module(s) must be done in that module's error-handler

The button in the template is configured with the hotkey 1 i.e. Alt + 1 runs is equivalent to clicking the button.

SyntaxBack to TOC

!Clear

Clears all values in the specified range.

Note - Only visible cells in the range will be cleared.

!Clear
Sheet1!E1
     

Putting just the sheet name clears out the whole sheet

!Clear
Sheet1
     

The command accepts all range formats that Excel accepts

!Clear
Sheet1!E2,Sheet1!F2
     
!Clear
Sheet1!E2:E4
     

You need to follow the same conventions - if the sheet name has a space, enclose it in single quotes

!Clear
'Sample Sheet'!E2:E4
     

Single quotes must not be used if you have only the sheet name.

!Clear
Sample Sheet
     

Expressions like [r+2], [r-3] and [r] evaluate to a value with r as the current row number.

!Clear
Sheet1!E[r+3]:E4
     

This above methods of entering a range hold for every inbuild command that takes a range as a parameter (or sub-parameter)

!ConvertFormulaToValues

Converts all formulas in the sheet to values.

Note - converting formulas to values clears all filters on the worksheet

!ConvertFormulaToValues
Sheet1
     

Converts the specified cell (or range) to values.

!ConvertFormulaToValues
Sheet1!C4
     

Use this to convert a formula column generated by an !Extract command to values. Filtering with formulas takes much much longer because a calculation runs everytime you set or clear the filter.

!Debug

Turns on debug mode for the run. For subsequent commands the row number and command are shown in the status bar

!Debug      

The optional parameter specifies how many seconds to pause before each command (upto a maximum of 5 seconds)

!Debug
2
     

!Dummy

A dummy statement doesn't do anything. Use it to replace commands. If a row with a command is deleted all row numbers in range parametes of subsequent commands may need to be updated (unless they are in the [r] format)

!Dummy      

You can also use it to comment out commands

!Dummy
!Extract
Sheet1!E2:E4
     

!Extract

Output the results of the query to the sheet specified. All values in the output range are cleared before the query is run.

!Extract
Sheet1
select * from USERS SQL Server Database name

If 2 consecutive Extract queries point to the same worksheet, data from the 2nd query is appended to the end of the 1st one.

!Extract
Sheet1
select * from USERS SQL Server Database name
!Extract
Sheet1
select * from OTHER_USERS SQL Server Database name

The first row of the worksheet is overwritten with the column names returned by the query.

All cells in the output sheet retain the formatting they had before the query was run.

If the column after the last field output by the query has a comment it is copied down to all the records. These column headers are not cleared when an !Extract runs. Use this to copy a formula down to all output rows. You can use the R1C1 or A1 notation, and named ranges.

If you put in a specific cell instead of a worksheet, the query outputs to the area with that cell as the top left corner. Column headers are not output in this case. Existing data is also not cleared, however it may be overwritten

!Extract
Sheet1!E4
select CURRENT_SETTING from SETTINGS SQL Server Database name

To extract data from an excel sheet (.xls, .xlsx, .xlsm and .xlsb) add the word Excel at the end of the command and enter the (relative or absolute) workbook path instead of the SQL Server name

!Extract
Sheet1
Excel
select * from [Sheet1$] ..Book1.xls  
  1. Each worksheet is treated as a table - use [<Sheetname>$] to refer to a specific worksheet.
  2. Extraction from an excel is subject to the same limitations as a normal Excel Import Data
    1. The column data type is decided by a sampling of the first n (decided by local registry settings) rows. Data from subsequent rows that do not match the column data format they will not be displayed.
    2. For memo fields (content > 255 characters) content beyond 255 characters will be clipped off.

For memo fields, a confirmation prompt warning about possible data loss is displayed. Include the word "ignore" at the end of the command to suppress this warning.

!Extract
Sheet1
Excel
Ignore
select * from [Sheet1$] ..Book1.xls  

To extract data from csv file, enter the folder path and use the file name in place of the table / worksheet name.

!Extract
Sheet1
Excel
select * from [file1.csv] ..  

To extract data from an Microsoft Access database add the word Access at the end of the command and enter the (relative or absolute) workbook path instead of the SQL Server name

!Extract
Sheet1
Access
select * from Table1 ..Database1.accdb  

It is the value of the cell that is used in all the commands. You can have a formula that evaluates to a query (or a command) and it will work just as a string that represents a query (or command) would.

!Filter

Sets the filter for the specified sheet. Each filter condition is on a separate line with the column name and condition separated by a semicolon.

!Filter
Sheet1
Defect Number;<10
Priority;=High
Assigned To;doej
   

Using the !Filter command without any conditions clears the filter for the specified sheet.

!Filter
Sheet1
     

!Form

This brings up a form with a line of text saying "Hello World"

!Form
Form Title
Label;Hello World!    

The 1st sub-parameter of the 2nd line can have 2 parts. The first is the title of the form and the 2nd (optional) part is the label of the submit button (the default value is Submit)

!Form
A Different Button:Next
Label;Hello World!    

An optional 2nd sub-parameter of the 2nd line allows you to set the width of the label and control section (minimum values are 80 and 165)

!Form
Form Title;100:300
Label;Hello World!    

The optional 3rd sub-parameter in the 2nd line is used to "fit" the form height. The height is automatically fit if there are more than 7 rows (including radio options)

!Form
Form Title;;fit
Label;Hello World!    

A blank line displays a blank line when showing the form

!Form
Form with a blank line
Label;Hello

Label;World!
   

You can also show a textbox. The 2nd sub-parameter is the label for that box.

If there is a control in the form, a mandatory 3rd line indicates where the form's data goes to. (the values are stored vertically, with a line for each control, including labels)

The default values for each control come from the same location.

!Form
A Form with a textbox
Configuration1!E[r]
Textbox;Enter User ID    

You can also have a password field.

Note - it is advisable to clear the cell to which the password was sent immediately after it's used.

!Form
A Form with a password box
Configuration1!E[r]
Password;Enter Password    

If the control label ends in * the field is mandatory. A form cannot be submitted unless all mandatory fields are filled.

!Form
A Form with mandatory fields
Configuration1!E2
Textbox;Enter User ID*
Password;Enter Password*
Textbox;Enter coupon
   

You can also add a checkbox. A checked box has value TRUE, an unchecked one is FALSE.

Note - Checkboxes cannot be mandatory.

!Form
A Form with a checkbox
Configuration1!E2
Checkbox;Check this box    

Or a pulldown. The label has 2 parts separated by a :. The 2nd part is a pointer to the first cell in a list of options. All entries till the first blank cell (vertically) are taken.

!Form
A Form with a pulldown
Configuration1!E2
Pulldown;List of values:Configuration1!H2    

And radio buttons. Radio buttons are are configured just like pulldowns. If the default value is missing the first option is selected automatically.

!Form
A Form with radio buttons
Configuration1!E2
Radio;Pick:Configuration1!H2    

And radio buttons. Radio buttons are are configured just like pulldowns. If the default value is missing the first option is selected automatically.

!Form
A Form with radio buttons
Configuration1!E2
Radio;Pick:Configuration1!H2    

You can have any number of controls on a form. If you have less than 7 rows (including radio options) they align to the bottom of the form.

!Form
A Form with lots of controls
Configuration1!E2
Label;Hello
Label;
Label;World
Textbox;Enter User ID
Password;Enter Password
Radio;Pick:Configuration1!H2
   

!Message

Displays a Yes / No message box. The 2nd line is the message. The 3rd line is where the output of the prompt goes to (TRUE if Yes, FALSE otherwise).

!Message
Had a nice day so far?
Configuration1!E[r]
     

!OnTrueMove

If the value in the 1st parameter range evaluates to True program control is moved 2nd parameter number of rows down

!OnTrueMove
Configuration1!E[r]
1
     

If the 2nd parameter is negative control moves back that many steps.

Note - you can use negative values to do a loop

!OnTrueMove
Configuration1!E[r]
-1
     

!RefreshPivots

Runs a Refresh Data on every pivot table in the specified sheet

!RefreshPivots
Sheet1
     

!Run

You can add your own module to the workbook and run it's subroutines / functions.

For subroutines use the !Run command followed by <Module name>.<Subroutine name>. The 2nd cell contains the arguments to the function - each argument on a new line.

!Run
Template.sTemplate
Hello
2
   

Functions can be called similarly. The 3rd line in the command cell is the cell to which the value returned by the function will go to.

!Run
Template.fTemplate
Configuration1!E20
Hello
2
   

!Set

Creates a variable called [myVariableName] and assigns the value <my variable value> to it. ExcelBricks will take the string [myVariableName] to mean whatever value it holds at that point of time.

!Set
[myVariableName]
<my variable value>    

ExcelBricks variables cannot be used in Excel formulas - they are NOT equivalent to named ranges.

Sets the Autofit configuration variable. True indicates that the worksheet columns should be autofit after an !Extract. Default is False

!Set
Autofit
TRUE    

If the 2nd line is not a configuration variable the cell or range specified is set to the indicated value. To set the cell(s) to a formula use a string starting with =

!Set
Configuration1!E[r]:G[r]
Hello World!    

Redirect ExcelBricks messages to the specified log file instead of <Workbook name>.log (when running in invisible mode)

!Set
Log File
Log_16051105    

When "No Screen Refresh" is True the screen is updated only at the end of the run.

By default, the screen is updated after the !Run command, and during the !Form command, password prompts and warnings.

When there are a lot of objects on the screen (charts, frames, etc.) setting "No Screen Refresh" to True to eliminates flicker.

!Set
No Screen Refresh
TRUE    

The "Run Timestamp Address" is the address of an (unlocked) cell to which the date and time of the run are entered on completion. If left blank the timestamp is not entered anywhere.

!Set
Run Timestamp Address
'Control Panel'!B5    

The "Run Message" value is displayed on successful completion of a run. If left blank the default Run log is displayed.

!Set
Run Message
Run completed!    

!StopIfBlank

The "Run Message" value is displayed on successful completion of a run. If left blank, the default Run log is displayed.

!StopIfBlank
Configuration1!C[r]:D[r]
Invalid value entered
     

!Version

Displays the program version number, the ExcelBricks API version and the ExcelBricks Syntax version

!Version      

Use the ExcelBricks API and the ExcelBricks syntax version to check for backward compatibility. If the ExcelBricks API version is a.x+, any calls to the ExcelBricks.sRunProgram subroutine from version a.x and above will work with this version. Similarly, if the ExcelBricks Syntax version is b.x+, all syntax commands supported by version b.x and above are supported by this version.

Additional ModulesBack to TOC

Additional modules are called using the !Run command. They can be imported into the workbook VBProject (Alt + F11 > Ctrl + M). Each subroutine or function in the additional module should have a corresponding wrapper subroutine and wrapper function. This wrapper must

  1. Takes a single argument which is a LineFeed (Chr(10)) separated list of arguments for the called subroutine or function.
  2. Split this parameter into multiple parameters
  3. Do a type conversion, if the specific subroutine or function argument is not a string
  4. Call the actual subroutine or function with these (type converted) parameters
  5. For functions, return the value of the called function
  6. Pass on any error messages from the called function or subroutine via the ExcelBricks.lngGlobalErrNumber and ExcelBricks.strGlobalErrDescription global variables.

Check the Template module to see how to build a wrapper for a subroutine (sTemplateWrapper for the sTemplate subroutine) and function (fTemplateWrapper for the fTemplate function).

Error numbers 7000 to 7100 are reserved for ExcelBricks. Additional modules can use a subset of these error numbers to control error handling functionality. A full list of such error numbers and their effect on error handling is given below

  • ExcelBricks.lngFullError - shows the standard log followed by a critical dialog with the error description. The behaviour
  • ExcelBricks.lngShowError - shows a critical dialog with the error description
  • ExcelBricks.lngShowErrorNoLine - shows a critical dialog with the error description but excludes the command line number
  • ExcelBricks.lngShowWarning - shows a warning dialog with the error description
  • ExcelBricks.lngShowMessage - shows an information dilaog with the error description
  • ExcelBricks.lngShowGlobal - shows an information dialog with the ExcelBricks.strGlobalErrDescription value (instead of the error description)
  • ExcelBricks.lngShowStatus - show the status alone

 

For example, to exit an additional module function with a warning message you would use

 Err.Raise ExcelBricks.lngShowWarning, , "Don't forget to get milk."

ExcelBricks turns off screen updates and automatic calculation while processing a command (caculation is run between commands. Screen updates are run between commands if No Screen Refresh is not set). So, external modules that need screen updates or automatic calculations must explicitly turn these on when they need it by the following bit of code

 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic

ExcelBricks exposes a number of commonly used functions that can be used by additional modules. A full list of such functions is given below

Clock functions

- these functions provide acces to the timer that ExcelBricks uses to keep track of the execution time

  • ExcelBricks.fGetTime - returns the total number of seconds since the start of the run.
  • ExcelBricks.sPauseClock - pauses the timer (for example, to exclude time spent waiting for user input)
  • ExcelBricks.sContinueClock - resumes the timer

Range functions

- convert a string to an excel range

  • ExcelBricks.fGetRange(strAddress) - returns the range specified by the string strAddress. You can use expressions like [r+2], [r-3] and [r] (as for the inbuilt commands). If you don't specify cell(s) you get a range spanning the entire worksheet.
  • ExcelBricks.fGetRangeFromCell(strStartAddress) - returns a columnar range starting at strStartAddress and ending at the first blank or 0 cell.

Logging and messaging functions

  • ExcelBricks.fAddLogEntry - add an entry to the log. The log is displayed only when the Run Message (refer to !Set) is blank.
  • ExcelBricks.fMessage - equivalent to VB MsgBox, but avoids screen repainting issues (because screen updating is normally turned off for performance resons during a run). When ExcelBricks is run in invisible mode (for example, by using an external script file that starts Excel with .Visible = false), messages are appended to a file <Workbook name>.log.

Path functions

  • ExcelBricks.fGetPath - returns an absolute path from a relative path (starting with . or ..)

For example, if an additional module function needs to show an informational message you would use

 ExcelBricks.fMessage "Hello World", vbInformation, "Hello World Title"

Standard ModulesBack to TOC

There are a few standard modules already available for commonly used functionality. These files (separate from the main excel workbook) can be imported if required and then used via the !Run command.

The functionality and parameters for each modules' subroutines / functions take are documented as comments in the module's .bas file. The numbers preceding each argument indicate the order in the function call.

For example the Pick module has the comment

 ' fPickFile - shows a Choose file dialog with title 1.strTitle and file name filter 2.strFilter

A sample call to this function is

!Run
Pick.fPickFileWrapper
Configuration1!F1
Choose a file to import
*.xls
   

Note that strTitle is the first argument, as indicated by the prefix 1. in the comment. The fPickFile function is called via the fPickFileWrapper function.

Known IssuesBack to TOC

For the list of issues and requested enhancements check https://excelbricks.codeplex.com/workitem/list/basic

How ToBack to TOC

In Excel 2007, macros may not enabled by default. So, you need to grant run permission to the ExcelBricks macro You do this by following the below steps

  1. When you open the ExcelBricks workbook you will see a Security Warning (usually just below the ribbon). Click the "Options" button.

  2. In the Microsoft Office Security Options dialog that opens up, select the "Enable this content" option and click the "OK" button.

This will allow the ExcelBricks macro to run. You will need to do this everytime you open the ExcelBricks workbook unless you open it from a "trusted" location (Excel Options > Trust Center > Microsoft Office Excel Trust Center > Trust Center Settings > Trusted Locations).

To add a button to a worksheet and link it to some code (that runs when it is clicked), see http://code.google.com/p/excelbricks/wiki/HowToAddButtonLinkCode

LicensingBack to TOC

ExcelBricks is free software: you can redistribute it and/or modify it under the terms of the "The MIT License" (see http://www.opensource.org/licenses/mit-license.php)

Copyright (C) 2011 by potato.peelings@gmail.com

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

The "Software" refers to the default ExcelBricks workbook and all standard modules that can be downloaded from the ExcelBricks project site. The "ExcelBricks project site" refers to https://code.google.com/p/excelbricks/, it's sub-folders and all content contained therein.

Last edited Feb 20, 2014 at 6:02 PM by potatopeelings, version 17