Iceberg Code

Thank you visiting ICB - Iceberg Code Blog
This is the place, where I'd like to share ideas and some useful code (VB, Excel, T-SQL, JavaScript, ...)
Table of Content

This is just a simple but useful function to self query Excel.
This mean that once you insert a named range you can query it,
with this Array Function:

Function SQL2Range(MySQL As String) 'Example: =SQL2Range("SELECT * FROM MyRange") 'On Error Resume Next ' Enable error-handling routine.      Dim objRsExcel As Object, sConn As String, strExcelPath As String, strExcelFileName As String, output      Set objRsExcel = CreateObject("ADODB.Recordset")      strExcelPath = ThisWorkbook.Path & "\"      strExcelFileName = ThisWorkbook.Name      objRsExcel.CursorLocation = 3 ' objRsExcel.CursorType = 0 'adOpenForwardOnly      objRsExcel.CursorType = 0 'adOpenStatic ' objRsExcel.CursorType = 0 'adOpenForwardOnly ' objRsExcel.CursorType = 0 'adOpenForwardOnly ' objRsExcel.LockType = 1      sConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & strExcelPath & strExcelFileName      objRsExcel.Open MySQL, sConn      output = objRsExcel.GetRows()      objRsExcel.Close      Set objRsExcel = Nothing SQL2Range = Application.WorksheetFunction.Transpose(output) End Function

Array Function are the one inserted in a range with Shift+Control+Enter

In the AddMenu.xls file you find a usefull worksheet
where you can set a personalised drop down Menu with all the commands to control your application.

This tool is full of tips and tricks in order to be as flexible as possible,
you don't need to modify the core of the code to change the menu
All the interface and control stay in the "MenuSheet" worksheet.

Let me explain my VBA way of Coding.
1) Let Excel do 99% of the work, so with little code you get great Result
2) Save all the variable in the worksheet, avoid constants and var inside the code
3) Let Spreadsheet and VBA communicate with Named Range
4) Use dynamic range to let expand the universe
5) Give the end user a lot of drop down menu in cells to avoid error
6) Copy and Paste ... Someone already did it
7) Divide and conquer ... small piece of code are smarter

That's said let see how to implement it in the real world
When you open AddMenu.xls workbook you are faced with a setting worksheet
where you can set everything to build your own Menu.

The table on the left contains all the info to give name and action to any button
The table can contain as many rows as you like (use dynamic range)

Then you have 3 variable on the top centre
the first 2 are to set the name and the position of the new Menu
The last one is a service variable that set the visibility
of the worksheet itself. When do you use this var? When you are done with all the
settings and you don't need anymore to show the setting worksheet, you can hide it
and give all to the final user.

The BarName Var can be chosen in a drop down menu, so you need a list of items.
The list is dynamic and refers to a named range.

So on the Excel side we are almost ready,
now lets give a name to all the ranges (I dont' have fantasy):

CaptionAction (table with list of all buttons - Dynamic)
BarName (where to put the new Menu)
MenuName (new Menu' name)
SheetVisible (to set the sheet visible)
ListCommandBars (all the CommandBar Name - Dynamic)

Why do I use named range? Why don't use absolute address?
Because VBA does not update if you change the position of a $A$1
style range. That's happen if you add/remove row/column
or you just move the cell with the data.
But if you give name to a range now, it's Excel that link the name
to the absolute position and when VBA recall it's an excel task to do
things right.
How to name range? there are 2 way the fast one and the slow one
Fast one is select a 2 column row range and follow the menus
[Insert][Name][Create]...
The slowest is [Insert][Name][Define].
Use the first when you have a list of Var (named on the left)
Use the second when you have a table to be named or
you want to build a Dynamic Named Range.

Setting a Dynamic Named Range is just a matter of Offset:

=MenuSheet!$A$2:OFFSET(MenuSheet!$A$2;COUNTA(MenuSheet!$A$2:$A$65536)-1;2)

It means that the table start at cell A2 and finish 3 column on the right (;2)
and down how many rows are not empty on the first column.
Once understand the logic, you can adapt to any direction (not just down)

To set the BarName Var you need to Populate the list of all CommandBars Names, there is a little sub:

Sub SetListCommandBars() ' This sub need to refer to the ListCommandBars named range ' work faster Application.ScreenUpdating = False ' get the first cell of the "ListCommandBars" range Set MyRange = Range("ListCommandBars").Cells(1) ' empty the range Range("ListCommandBars").Clear ' populate the range with all CommandBars Name For i = 1 To CommandBars.Count      MyRange.Offset(i - 1) = CommandBars(i).Name Next i Application.ScreenUpdating = True End Sub

The sub to work needs the "ListCommandBars" named range in the worksheet:

=MenuSheet!$M$2:OFFSET(MenuSheet!$M$2;COUNTA(MenuSheet!$M$2:$M$65536)-1;0)

Now lets Code!
In the VBA Module there are a lot of small functions and sub to get ready to go
and to check some error, but the core is the one that's add the new menu and all
the buttons in it:

Sub AddMenu() 'create a new menu item with all the subMenu SetVar 'Take all the Var from the worksheet and makes avaibale in VBA Dim i As Integer ' If the menu already exists just exit      If MenuExists(BarName, MenuName) Then Exit Sub ' The menu does not exists so add it ...      Set mypopup = CommandBars(BarName).Controls.Add(Type:=msoControlPopup)      mypopup.Caption = MenuName ' Add the SubMenu      For Each Cell In CaptionActionFirstCol      ' if you preffer, you can avoid double names!      If Len(Cell.Value) > 0 And Not SubMenuExists(BarName, MenuName, Cell.Value) Then      'If Len(Cell.Value) > 0 Then 'simplest one      Set myitem = mypopup.Controls.Add(Type:=msoControlButton)          With myitem              .Caption = Cell.Value              If Len(Cell.Offset(0, 1).Value) > 0 Then .OnAction = Cell.Offset(0, 1).Value              If Cell.Offset(0, 2).Value > 0 Then .FaceId = Cell.Offset(0, 2).Value          End With      End If      Next Cell End Sub

It looks very simple and it is, but remember that 99% of the work
has been done by Excel!

Download it Now!