3

I'm having troubles passing an Array from user-defined Type to a Function:

Defined Type (in a standard Module):

Public Type JPrinters
    Available() As String
    Default As String
End Type

Function to make a list of all available printers (std Module):

Function ListPrinters() As JPrinters

    Dim GetPrinters As Object: Set GetPrinters = CreateObject("WScript.Network").EnumPrinterConnections     
    Dim i As Integer
    Dim j() As String  'Array of Printer Names
    ReDim j(0 To GetPrinters.Count \ 2 - 1)

    For i = 0 To UBound(j)
        'Load this array element with the Name from the list
        j(i) = GetPrinters.Item(i * 2 + 1)
    Next i

    Dim k As JPrinters
    With k
        .Available = j
        .Default = Application.ActivePrinter
    End With

    ListPrinters = k
End Function

Sub to populate a ComboBox by an Array (which works fine, normally – std Module):

Sub PopulateComboBoxBy2DArray(Ctrl As Control, ByVal Source_1D_2D_Array As String, Optional DefaultValue As String)
    Ctrl.List = Source_1D_2D_Array
    If DefaultValue Then
        Ctrl.Value = DefaultValue
    End If
End Sub

Then I try to call everything on Workbook_Open:

Private Sub Workbook_Open()
    Dim Prt As JPrinters
    Prt = ListPrinters
    PopulateComboBoxBy2DArray List1.PrinterList, Prt.Available, Prt.Default
End Sub
  • All modules have Option Explicit defined.

And it just gives my Compile error: Type mismatch. I tried a lot but cannot figure out why it won't accept the Prt.Available Array. Any ideas?

(I will also be glad for any other suggestions regarding the code.)

Thank you,

J

Improve this question
2
  • Have you tried "ByVal Source_1D_2D_Array As Variant"? Commented Mar 29, 2016 at 10:35
  • Yes, but it does not work either – the whole line PopulateComboBoxBy2DArray List1.PrinterList, Prt.Available, Prt.Default is selected and Watch says <Expression not defined in context>. Commented Mar 29, 2016 at 10:44

2 Answers 2

Reset to default
2

Resolved (thanks to Dave from Mr. Excel forums). It goes like this:

Type:

Type J_Type_Printers
    Available() As Variant
    Default As Variant
End Type

Function:

Function J_Fx_ListPrinters() As J_Type_Printers
    Dim GetPrinters As Object
    Dim i As Integer
    Dim j() As Variant 'Array of Printer Names
    Dim k As J_Type_Printers

    Set GetPrinters = CreateObject("WScript.Network").EnumPrinterConnections
    ReDim j(0 To GetPrinters.Count \ 2 - 1)

    For i = 0 To UBound(j)
        'Load this array element with the Name from the list
        j(i) = GetPrinters.Item(i * 2 + 1)
    Next i

    With k
        .Available = j
        .Default = Application.ActivePrinter
    End With
    J_Fx_ListPrinters = k
End Function

Sub to populate combobox by array contents:

Sub J_Sub_Controls_ComboBox_ListArray(ByVal Ctrl As ComboBox, ByVal SrcArray As Variant, Optional DefaultValue As Variant)
    Ctrl.List = SrcArray
    If Not IsMissing(DefaultValue) Then Ctrl.Value = DefaultValue
End Sub

Call on Workbook_Open:

Private Sub Workbook_Open()
    Dim Prt As J_Type_Printers: Prt = J_Fx_ListPrinters
    J_Sub_Controls_ComboBox_ListArray List1.PrinterList, Prt.Available, Prt.Default
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I believe the problem is the declaration of the Source_1D_2D_Array parameter. It is declared as string and it needs to be a string array. The type mismatch is because you are passing an string array to a string parameter.

Also the check for the DefaultValue needs to be a comparison. I think you want:

Sub PopulateComboBoxBy2DArray(Ctrl As Control, ByRef Source_1D_2D_Array() As String, Optional DefaultValue As String = "")
    Ctrl.List = Source_1D_2D_Array
    If DefaultValue<>"" Then
        Ctrl.Value = DefaultValue
    End If
End Sub
Improve this answer

5 Comments

Meaning that the error stays the same? Type mismatch? Can you also verify what line is giving the error?
It's error 13: Type Mismatch... in fact, this was as far as could get because at least Prt.Available contains desired data. But the line marked by the debugger stays the same I mentioned in my comment above...
It may be related to List1.PrinterList. From your question we can't tell what that is. Are you sure it is of type "Control"?
I tried ComboBox, OLEObject, even Object types..., no effect at all. Still Error 13. The ComboBox is placed on Sheet1 ("List1" is the equivalent name in my language). The funny thing is that it had worked before I separated it to functions I wanted to reuse. I think the UDT is the problem.
If List1 is a ComboBox, then what is List1.PrinterList? I am not familiar with that property of a ComboBox. Did you mean to pass List1 to your populate function?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.