Excel Vba How to Update Listbox With Class Module
I've been asking this at my usual VBA information sources but no solution as yet so I thought its at least worth asking here.
The questions is, is the process of passing the reference of a form control (in my case again a listbox) to a function in a class any different to how you do it for a function in a module?
The reason I ask is I am encountering an invalid use of null error and my first suspicion is that the listbox reference hasn't worked. I have other classes in which I have no problem working with the listboxes however the difference is that they depend on a listbox item being selected and the listbox enter event provide the class with the name of the listbox. My current situation is that I am looping through an array in a function within a class which creates text elements from each array item. As can be see below from my userform, I have 3No. arrays - 1 for each listbox in the 3rd frame. Array row 0 populates the associated listbox column 0. My current task is, as I loop through the bounds of the array, I want to capture the listbox column 3 item value (RGB colour) by a correlation between Array column number and listbox row number. This image shows the userform immediately after a valid XLSX file has been selected and opened:
The text elements are being created in a transposed format from left to right, once completed all headers are stacked vertically to the left and the remaining array values are to the right (hence the order of the Row/Column looping code which can be seen in the TextFromArray function further below).
Referring back to my question, I pass a reference to a form control in a module like this (IIRC Jan Slegr provided myself with this method in a previous thread):
Private Sub TB_TextHeight_Change() ValidateTextSize Me.TB_TextHeight End Sub
and the function declaration:
Public Sub ValidateTextSize(ByVal tBox As TextBox)
This works fine without issue. In my class however I have:
Private Sub IPrimitiveCommandEvents_DataPoint(Point As Point3d, ByVal View As View) Dim oText_Chaingage As TextElement Dim oText_LB1 As TextElement Dim oText_LB2 As TextElement Dim oText_LB3 As TextElement Dim i As Long CommandState.AccuDrawHints.SetOrigin Point If m_nWorksheets = 1 Then Set oText_Chaingage = WriteChainage(Point, ArrayChainage) Set oText_LB1 = TextFromArray(Point, ArraysSorted(i), FrmMain.LB_Sheet1) ElseIf m_nWorksheets = 3 Then Set oText_Chaingage = WriteChainage(Point, ArrayChainage) For i = LBound(ArraysSorted, 1) To UBound(ArraysSorted, 1) Select Case i Case 0 Set oText_LB1 = TextFromArray(Point, ArraysSorted(0), FrmMain.LB_Sheet1) Case 1 Set oText_LB2 = TextFromArray(Point, ArraysSorted(1), FrmMain.LB_Sheet2) Case 2 Set oText_LB3 = TextFromArray(Point, ArraysSorted(2), FrmMain.LB_Sheet3) End Select Next i End If End Sub
and my Function (partial code) which should be receiving the listbox reference:
Private Function TextFromArray(Point As Point3d, ByRef CurrArray As Variant, ByVal LBox As ListBox) Dim R As Long Dim C As Long Dim oFont As Font Dim oText As TextElement Dim DataPoint As Point3d Dim ItemOffset As Point3d Dim Header As Point3d Dim FirstItem As Point3d Dim sRGBVals() As String Dim sRGBVal As String For C = LBound(CurrArray, 2) To UBound(CurrArray, 2) For R = LBound(CurrArray, 1) To UBound(CurrArray, 1) sRGBVal = CStr(LBox.List(C, 3)) Debug.Print sRGBVal Stop sRGBVals() = Split(sRGBVal, ",", , vbTextCompare)
The invalid use of null error occurs as below:
This is as far I have gotten so I'm hoping someone can advise where I have gone wrong.
FWIW: I was previously supplying the RGB values as a parameter to TextFromArray via IPrimitiveCommandEvents_DataPoint and everything worked fine so I know the rest of the class code is correct, I just need to know how the access the listboxes from the class if it is indeed different to the module.
Thanks
Excel Vba How to Update Listbox With Class Module
Source: https://communities.bentley.com/products/programming/microstation_programming/f/microstation-programming---forum/132572/v8i-vba-passing-listbox-reference-to-class-different-to-a-module