Maintain RS created from Add-In function

[ClimoC]

Hi

I wrote this:

Global CRS As ADODB.Recordset
Public ColType As Integer, ColSize As Integer
Public Arr1() As Variant, Arr2() As Variant, RuleList() As Variant
Public frmEvents As Boolean


Public Function BuildRecordSet()


Dim i As Long, z As Long, XCol As Long, YRow As Long, FRow As Long, FUCV1 As Range, FUCV2 As Range, FUCV3 As Range, COlTypeCOll As New Scripting.Dictionary, StrRplcColHead As String, CollTypeSum As Double


Set CRS = New ADODB.Recordset
With Selection
XCol = .End(xlToRight).Column 'Width in Columns
YRow = .Rows.Count + .Rows(1).Row - 1
FRow = .Rows(1).Row


For i = .Columns(1).Column To XCol


'Test 3 values from the field, to determine Data type guestimate
Set FUCV1 = Cells(FRow, i).End(xlDown)
Set FUCV2 = Cells(FRow, i).Offset(1, 0)
Set FUCV3 = Cells(WorksheetFunction.RandBetween(FUCV2.Row, FUCV1.Row), i)


Select Case TypeName(FUCV1.Value)
            Case "Double"
                        CollTypeSum = 1
            Case "String"
                        CollTypeSum = 100
            Case "Date"
                        CollTypeSum = 10
            Case "Boolen"
                        CollTypeSum = 0.1
            Case Else
                        CollTypeSum = 100
End Select


Select Case TypeName(FUCV2.Value)
            Case "Double"
                        CollTypeSum = CollTypeSum + 1
            Case "String"
                        CollTypeSum = CollTypeSum + 100
            Case "Date"
                        CollTypeSum = CollTypeSum + 10
            Case "Boolen"
                        CollTypeSum = CollTypeSum + 0.1
            Case Else
                        CollTypeSum = CollTypeSum + 100
End Select


Select Case TypeName(FUCV3.Value)
            Case "Double"
                        CollTypeSum = CollTypeSum + 1
            Case "String"
                        CollTypeSum = CollTypeSum + 100
            Case "Date"
                        CollTypeSum = CollTypeSum + 10
            Case "Boolen"
                        CollTypeSum = CollTypeSum + 0.1
            Case Else
                        CollTypeSum = CollTypeSum + 100
End Select


CRSFirstForm.ComboBox1.AddItem Cells(FRow, i).Value
CRSFirstForm.ListBox1.AddItem Cells(FRow, i).Value


Select Case CollTypeSum
                Case Is < 1
                            CRSFirstForm.ListBox1.List(CRSFirstForm.ListBox1.ListCount - 1, 1) = "Boolean"
                Case 1.3 To 3
                            CRSFirstForm.ListBox1.List(CRSFirstForm.ListBox1.ListCount - 1, 1) = "Double"
                Case 10.3 To 21
                            CRSFirstForm.ListBox1.List(CRSFirstForm.ListBox1.ListCount - 1, 1) = "~String"
                Case 30
                            CRSFirstForm.ListBox1.List(CRSFirstForm.ListBox1.ListCount - 1, 1) = "Date"
                Case Is > 100
                            CRSFirstForm.ListBox1.List(CRSFirstForm.ListBox1.ListCount - 1, 1) = "~String"
                Case Else
                            CRSFirstForm.ListBox1.List(CRSFirstForm.ListBox1.ListCount - 1, 1) = "~String"
End Select


Next i


CRSFirstForm.ComboBox2.AddItem "Double"
CRSFirstForm.ComboBox2.AddItem "String"
CRSFirstForm.ComboBox2.AddItem "Date"
CRSFirstForm.ComboBox2.AddItem "Boolean"


CRSFirstForm.Show


End With


'Build
With CRS


For z = 1 To UBound(Arr1())
        CRS.Fields.Append CStr(Arr1(z, 1)), CInt(Arr1(z, 2)), CInt(Arr1(z, 3))
Next
        
        .LockType = adLockOptimistic
        .CursorType = 3
        .CursorLocation = 3
        .CacheSize = 1000
        .Open
        
On Error Resume Next
For z = (FRow + 1) To YRow
        CRS.AddNew
        For i = Selection.Columns(1).Column To XCol
                Err.Clear
                CRS.Fields(i - Selection.Columns(1).Column).Value = WB.Sheets(1).Cells(z, i).Value
                If Err.Number <> 0 Then
                        Select Case CRS.Fields(i - 1).Type
                                Case Is < 1
                                            CRS.Fields(i - 1).Value = 0
                                Case 1.3 To 3
                                            CRS.Fields(i - 1).Value = 0#
                                Case 10.3 To 21
                                            CRS.Fields(i - 1).Value = ""
                                Case 30
                                            CRS.Fields(i - 1).Value = CDate("01/01/1901")
                                Case Is > 100
                                            CRS.Fields(i - 1).Value = ""
                                Case Else
                                            CRS.Fields(i - 1).Value = ""
                        End Select
                End If
        Next
Next
Err.Clear
On Error GoTo 0
        .Update
End With
CRS.MoveFirst


End Function




Public Sub RibbonCall(control As IRibbonControl)
    
    Select Case control.ID
    Case "addCRS"
            BuildRecordSet
    End Select
    
End Sub

which works fine when it's an open workbook (or at least, it did). I then saved it as an add-in, XML'd in a Ribbon button, and whilst it all seems to execute fine, when the call procedure is finished, the RecordSet Object is Nothing.

Thoughts? Basic principle I'm missing? I've done similar with a workbook before, but the Recordset is created in the document (and maintains itself when you switch between workbooks/sheets, you come back and it's still there, in memory)

Help? :D

C

nb: When I step through it, it's still there right up to 'End Sub' on the Ribbon callback. Then, immediate-window '?CRS is nothing' becomes True. This didn't happen with my other Button-make-recordset - the only difference I can see is that this is for an Add-in, not a workbook.

 

[ClimoC]

Don't know why exactly (have a vague conceptual idea of why), but it works if the line

Set CRS = New ADODB.RecordSet

Goes in the RibbonCall Function.

The RS is maintained when the function finishes.

 

[xenou]

Don't see why per se but it may be the ribbon is playing some havoc with the vba environment state.

Two questions:

Public Function BuildRecordSet()
Dim i As Long ....
[COLOR="#FF0000"]Set CRS = New ADODB.Recordset[/COLOR]

1) This seems a little dangerous to set a new recordset in your subroutine - the recordset will be overwritten if it already exists. I'm not sure I see why you are using a public variable.


2) Why not just store the data in a hidden Excel sheet (i.e., a table). That would be more persistent, hence more robust in case of errors (you won't be dependent on memory state). In fact, given that you are reading the data from a spreadsheet it already exists in your file. Why is that you are creating the recordset here?


Also, for what it's worth, when I declare a Global variable I get an error. VBA uses Public. I can't find Global in the VBA reference. Do you normally use the Global keyword? [Edit: actually, Global appears to be allowed in a module but not in a sheet - so that's why I got the error as I must have tried it in a sheet class]

Edit:
P.S., Is this a spelling error?

            Case "Boolen"
                        CollTypeSum = CollTypeSum + 0.1

 

[ClimoC]

Don't see why per se but it may be the ribbon is playing some havoc with the vba environment state.

Two questions:

Public Function BuildRecordSet()
Dim i As Long ....
[COLOR=#FF0000]Set CRS = New ADODB.Recordset[/COLOR]

1) This seems a little dangerous to set a new recordset in your subroutine - the recordset will be overwritten if it already exists. I'm not sure I see why you are using a public variable.


2) Why not just store the data in a hidden Excel sheet (i.e., a table). That would be more persistent, hence more robust in case of errors (you won't be dependent on memory state). In fact, given that you are reading the data from a spreadsheet it already exists in your file. Why is that you are creating the recordset here?


Also, for what it's worth, when I declare a Global variable I get an error. VBA uses Public. I can't find Global in the VBA reference. Do you normally use the Global keyword?

Edit:
P.S., Is this a spelling error?

            Case "Boolen"
                        CollTypeSum = CollTypeSum + 0.1

Ooh - yes, thank you - yes that should be 'Boolean'...

The main purpose of this is just a quick and universal way of slurping something into a recordset, so that I can do quick custom reporting-style queries (filter, sum, loop, unfilter, or what have you) instead of writing custom code each time creating the RS, appending the columns, etc. I hadn't really thought passed having one, though in actual fact I suppose having two could be quite useful - open two docs, slurp em up with the button (perhaps some sort of 'If RS1 is nothing then set RS1.... else set RS2...'), then I can get straight into the actual coding (which takes usually a lot less time that creating the **** thing in the first place)

As far as Global/Public is concerned, I was always under the assumption (wrongly, maybe) that in order to use an Object/Variable in another subroutine/workbook, you had to make it 'Public'-ally available. Global I thought was just a more encompassing form of that which covered a few avenues that Public might not work. (TBH, I interchange using Public and Global all the time without much thought about it and I don't know which one would fail where and why... there's reading on it somewhere but I've never encountered major issues!)

But yes, I use Global often enough...

 

[xenou]

It's curious about the Global. I'll have to check my VBA reference when I'm home tonight to see if Lomax mentions this.

 

[xenou]

So, I have discovered that Global is for compatibility with VB 4.0. Circa 1998 MS introduced Public with VB 5.0 (MS discontinued VB development with VB 6.0 though they have introduced VBA 7.0 so VBA will work with 64-bit systems). It's hard to say exactly what the precise difference is, but it seems that MS wanted to address (or sweep under the rug) some issues having to do with multi-threaded execution of code, possibly brought on by the introduction of ActiveX controls in the Visual Basic code environment. I believe the old Global was the only kind of variable that you could use in VB 4.0 in the declarations area. Now we have Public, Private, Friend, or Dim so there's a wider range of scopes available for variables in the declarations area.

 

[ClimoC]

So, I have discovered that Global is for compatibility with VB 4.0. Circa 1998 MS introduced Public with VB 5.0 (MS discontinued VB development with VB 6.0 though they have introduced VBA 7.0 so VBA will work with 64-bit systems). It's hard to say exactly what the precise difference is, but it seems that MS wanted to address (or sweep under the rug) some issues having to do with multi-threaded execution of code, possibly brought on by the introduction of ActiveX controls in the Visual Basic code environment. I believe the old Global was the only kind of variable that you could use in VB 4.0 in the declarations area. Now we have Public, Private, Friend, or Dim so there's a wider range of scopes available for variables in the declarations area.

Huh. Interesting that. Well I don't think anyone at work is still on anything older than XP SP3 so no real need for Global anymore I guess.