Passing an iGrid to a sub/function in MS Office VBA

Welcome Guest! To enable all features please Login or Register.

Rahul
Newbie Topic Starter

2012-10-04T19:14:13Z

Hello,

I have downloaded the iGrid demo and have been playing around with it. My company is sick of the MSCOMCTL.OCX updates since every update seems to break any app with listviews.
However I'm stuck in my code since I just can't figure out how to pass an iGrid to a function/sub. It's bizarre that it's not working.

I have 9 listviews on my form (and hence will have 9 iGrids). It's imperative that I be able to pass an iGrid around else it will be a nightmare writing repetitive code.

Here's an example of what I'm trying to do (just doing an initial setup - adding columns and setting some properties for all iGrids). However I always get a type mismatch error.
If I try something like

Dim ig as iGrid
Set ig = igAuds

that also fails.

Private Sub UserForm_Initialize()
  SetupIgrid igAuds, "File Name;140;Database ID;0;Computer;100;Workbook;100;UnitID;0"
  SetupIgrid...
  SetupIgrid...
  SetupIgrid...
End Sub


Sub SetupIgrid(ig As iGrid, columns As String)
  ig.RowMode = True
  ig.MultiSelect = True
  ig.Appearance = igAppearanceFlat
  ig.Editable = False
  
  Dim i As Byte
  Dim values: values = Split(columns, ";")
  For i = 0 To UBound(values) Step 2
    ig.AddCol sHeader:=values(i), lWidth:=values(i + 1), bVisible:=IIf(values(i + 1) > 0, True, False)
  Next
End Sub

Edited by moderator 2019-05-11T06:28:43Z | Reason: Not specified

Igor/10Tec
Administration

2012-10-05T05:47:26Z

This feature definitely works in "big" VB (VB6), but it seems in VBA there is no way to make it working.

If you search the Internet, you'll find a lot of mentions of this problems, for example:

re: VBA Problem - Passing a TextBox Object as a parameter
http://www.pcreview.co.uk/forums/re-vba-problem-passing-textbox-object-parameter-t922191.html

VBA - Create an instance of a class and pass it to a function - how?
http://www.mrexcel.com/forum/excel-questions/484519-visual-basic-applications-create-instance-class-pass-function-how.html

So, as you can see, this is not a problem of iGrid itself - something is wrong inside MS Office VBA.

I've tried these recipes, and all other methods I know, but nothing helped. Even such undocumented techniques like passing object Long pointers from one sub to another (we can get with the VB ObjPtr() func and convert them back to real object using the WinAPI CopyMemory call).

If you'll find a solution, or someone else knows how to do that - post this info here, please.

Edited by user 2012-10-05T15:09:40Z | Reason: Not specified

Rahul
Newbie Topic Starter

2012-10-05T09:04:19Z

Igor,

Thanks for your reply. I created a brand new project, added a button and an iGrid to it but had the same issue with it as soon as the SetupIgrid subroutine was called. I have emailed that very same code to the email address contact@10Tec.com

As I've mentioned in the email, oddly enough it works fine if in the input to the sub, ig is declared as variant

So, if I had
Sub SetupIgrid(ig, columns As String)
instead of
Sub SetupIgrid(ig As iGrid, columns As String)

it works fine, but ofcourse I lose intellisense.

Any help you can offer will be appreciated.

chris
Newbie

2012-10-18T09:26:32Z

Hi Rahul,

in MS Access you can do this very easy with the following function

Public Sub SetupIgrid (s_Name As String, s_Grid As String, columns As String )
  dim i as byte
  With Forms(s_Name).Controls(s_Grid)
    .BeginUpdate
    .Clear bRemoveCols:=True
    .FocusRect = False
    .Editable = False
    .DefaultAutoGroupRowExpanded = False
    .PrefixGroupValues = False
    .Header.SortIconStyle = igSortIconSolid
    .Header.SolidSortIconColor = rgb(225, 68, 0)
    Dim values: values = Split(columns, ";")
    For i = 0 To UBound(values) Step 2
    .AddCol sHeader:=values(i), lWidth:=values(i + 1), bVisible:=IIf(values(i + 1) > 0, True, False)
    Next
    .EndUpdate
  End With
End Sub

In a form you call the function like this:

SetupIgrid me.name, igAuds, "File Name;140;Database ID;0;Computer;100;Workbook;100;UnitID;0"

Hope this help

Edited by moderator 2012-10-22T08:04:48Z | Reason: Not specified

chris
Newbie

2012-10-18T10:22:19Z

A more general solution is to create a table (or an ini File), where you store all your settings

Columns:

DATASOURCE	AS STRING = the name of the table, view, ...
DB_FIELDNAME AS STRING = the name of the database field
DB_DATATYPE AS STRING =  the type of the database field (number, boool, string, ....)
IG_HEADER_TEXT AS STRING = The headertext for the column
IG_HEADER_WIDTH AS NUMBER = the width of the column
IG_SORT_ORDER AS NUMBER = the default sort order
IG_COL_VISIBLE AS NUMBER (0,1) = is the column visible?
IG_E_ALLIGN_H AS NUMBER = allignment of the column
IG_E_CELLTYP AS NUMBER = celltype
IG_COL_TAG AS STRING = optional tag
IG_FORMAT_STRING AS STRING = optional format string, maybe "0
IG_COLUMN_ORDER AS NUMBER = in which order do you want to display the columns

You can call the function from a form with

Private Sub Form_Load()
dim p_Sql as string
p_Sql = " select * from myInitable where datasource = 'myDataSource' order by ig_column_order"
gf_SetupColumns "myFormName","myIgrid",p_Sql)

End Sub

Public Function gf_SetupColumns(s_Form As String, s_Control As String, s_Source As String)

  Dim m_RS As ADODB.Recordset
  Dim m_DBConnection As ADODB.Connection 
  Set m_RS = CreateObject("ADODB.Recordset")
  Set m_DBConnection = CreateObject("ADODB.Connection")
  Set m_DBConnection = CurrentProject.AccessConnection
    
  With Forms(s_Form).Controls(s_Control)
    
    m_RS.Open s_Source, m_DBConnection, adOpenDynamic, adLockOptimistic
    If Not (m_RS.EOF And m_RS.BOF) Then
      m_RS.MoveFirst
      Do
        ' add a column
        With .AddCol(sKey:=m_RS!DB_FIELDNAME, _
                     sHeader:=m_RS!IG_HEADER_TEXT, _
                     lWidth:=m_RS!IG_HEADER_WIDTH, _
                     eSortOrderDefault:=m_RS!IG_SORT_ORDER, _
                     bvisible:=m_RS!IG_COL_VISIBLE, _
                     vtag:=m_RS!IG_TAG _
                         )
        ' do something special for datatypes
          If m_RS!DB_DATATYPE = "BOOL" Then
            .bCheckVisible = True
            .eCheckState = igCheckStateUnchecked
            .eCheckPos = igCheckPosCenter
            .eTypeFlags = igCheckBoxFlat
          Else
            .eType = m_RS!IG_E_CELLTYP
            .eAlignH = m_RS!IG_E_ALLIGN_H
          End If
          
          ' apply formatstring if available 
          If Len(m_RS!IG_FORMAT_STRING & "") > 0 Then
            .sFmtString = m_RS!IG_FORMAT_STRING
          End If
        End With
        m_RS.MoveNext
      Loop Until m_RS.EOF
    End If
    m_RS.Close
  End With
  Set m_RS = Nothing
End Function

Edited by moderator 2012-10-22T08:05:33Z | Reason: Not specified

Igor/10Tec
Administration

2012-10-18T10:48:27Z

Here is the discussion of this problem we initiated on stackoverflow, but still there is no good solution:

http://stackoverflow.com/questions/12748897/passing-activex-control-as-parameter-to-vba-function-causes-type-mismatch-erro

The problem is deep inside MS Excel VBA, and this stackoverflow thread mentions an "Extender" object which causes the problem. This is a general issue for all ActiveX controls, not only for iGrid. Unfortunately, it seems, we will never find a solution for the topic problem because of this wrapper stuff in VBA.

Edited by user 2012-10-29T07:22:37Z | Reason: Not specified

andy11252
Newbie

2013-03-01T10:03:36Z

Can pass it as a Control. Obviously no intelli-sense for design unless you write the code with it as an iGrid and change it once your happy to a control.

Seems to me, iGrid would need to hold its data in something like an iGridObject, so this could be passed rather than the control itself. Seems happy enough with CellObject etc.

Marc
Member

2013-08-30T08:54:04Z

Originally Posted by: Rahul

I have downloaded the iGrid demo and have been playing around with it. My company is sick of the MSCOMCTL.OCX updates since every update seems to break any app with listviews.
However I'm stuck in my code since I just can't figure out how to pass an iGrid to a function/sub. It's bizarre that it's not working.

I have 9 listviews on my form (and hence will have 9 iGrids). It's imperative that I be able to pass an iGrid around else it will be a nightmare writing repetitive code.

Here's an example of what I'm trying to do (just doing an initial setup - adding columns and setting some properties for all iGrids). However I always get a type mismatch error.
If I try something like

Dim ig as iGrid
Set ig = igAuds

that also fails.

Private Sub UserForm_Initialize()
SetupIgrid igAuds, "File Name;140;Database ID;0;Computer;100;Workbook;100;UnitID;0"
SetupIgrid...
...
End Sub

Sub SetupIgrid(ig As iGrid, columns As String)
ig.RowMode = True
ig.MultiSelect = True
ig.Appearance = igAppearanceFlat
ig.Editable = False
...
End Sub

I often pass iGrid objects as a reference to subs/function in Access 2010 VBA code. It works like this:


Dim ig as iGrid
Set ig = igAuds.Object 'use igAuds.Object instead of igAuds!!!

assuming 'igAuds' is the control on your form.
That way you will be even able to use IntelliSense.

Edited by user 2013-08-30T09:04:54Z | Reason: Formating

Important Information:

The 10Tec Forum uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close