Click here to Skip to main content
14,662,801 members
Rate this:
Please Sign up or sign in to vote.
I'm trying to create a custom control in Visual Studio for use in VBA. I found the following link and tried to follow it:[^]

Everything in the link appears to work fine up until the point where it describes creating an app to call and test the DLL.

In my VBA (NOT VB) app, I added a reference to the DLL. Then, I created a form, as well as a sub that does nothing but show the form. When I try to run this VBA app, however, I get a "User-defined type not defined" error on the two declarations in the attached snippet.

Option Explicit
Public moTempClass As PhysServer2.NET_Temperature
Public moTemp As PhysServer2.iTemperature
Sub Main()
End Sub

So, my question is: Should this work in VBA (NOT VB)? Or, is there something special I have to do with a custom control to get it to work in VBA (NOT VB).

Thanks, in advance, for any help and suggestions.
Updated 10-Mar-11 14:44pm
Maciej Los 17-Mar-11 17:00pm
Which version of VS2008 do you have: Express, Standard, Pro version?
technotrope 17-Mar-11 17:55pm
Maciej Los 4-Apr-11 14:59pm
Hello! I can help you to create custom control in VBA. I'm trying to solve your problem in VB.NET, but unsuccessful yet.
technotrope 4-Apr-11 16:03pm
Losmac -

Thanks for your reply. I guess I'm not quite sure what you mean by "create custom control in VBA." Do you mean "CREATE the control," or "get the already-created control to WORK" in VBA?

Thanks, again, for any help you can offer.
Maciej Los 5-Apr-11 14:09pm
Create = create, not use existing control to work with VBA. For example i can help you to create custom control in MS Excel: form to calculate temperature from Celsius to Fahrenheit.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 2

1) Open MS Excel
2) ALT+F11 to open VBA code editor
3) Add new class (menu Insert->Class module) and change it name to: ClsCalcTemp
4) Copy and paste code below:
Option Explicit

Private mdblCelsius As Double
Private mdblFahrenheit As Double

Private Sub Class_Initialize()
    Celsius = 0
    Fahrenheit = 0
End Sub

Public Property Let Celsius(dCelcius As Double)
    mdblCelsius = dCelcius
End Property

Public Property Get Celsius() As Double
    Celsius = mdblCelsius
End Property

Public Property Let Fahrenheit(dFahrenheit As Double)
    mdblFahrenheit = dFahrenheit
End Property

Public Property Get Fahrenheit() As Double
    Fahrenheit = mdblFahrenheit
End Property

Public Function F2C(ByVal dFahrenheit As Double) As Double
    mdblFahrenheit = dFahrenheit
    mdblCelsius = ((dFahrenheit * 9) / 5) + 32
    F2C = mdblCelsius
End Function

Public Function C2F(ByVal dCelsius As Double) As Double
    mdblCelsius = dCelsius
    mdblFahrenheit = ((dCelsius - 32) * 5) / 9
    C2F = mdblFahrenheit
End Function

Private Sub Class_Terminate()
    mdblCelsius = 0
    mdblFahrenheit = 0
End Sub

5) Now, insert new UserForm (menu Insert->UserForm) and change it name to: CalcTempFrm
6) Insert controls:
- Frame /set property Caption:=Choose option/,
- ListBox (on he frame; this could be our option group) /set property Name:=LstOptTemp/ ,
- Label (below Frame) /set property Caption:=Temperature/,
- TextBox (on the right side of Label) /set property Name:=TxtTemperature/
- Label (below Textbox) /set property Name:=LblResult/
7) Copy and paste code below:
Option Explicit

Dim oTCalc As New ClsCalcTemp

Private Sub UserForm_Initialize()

    'add options
    Me.LstOptTemp.AddItem "Celsius -> Fahrenheit"
    Me.LstOptTemp.AddItem "Fahrenheit -> Celsius"

End Sub

Private Sub CmdCalculate_Click()
Dim iOpt As Integer, dblTemperature As Double, dblResult As Double

On Error GoTo Err_CmdCalculate_Click

iOpt = Me.LstOptTemp.ListIndex
dblTemperature = CDbl(Me.TxtTemperature)

Select Case iOpt
    Case -1
        MsgBox "Select option!", vbInformation, "Message..."
    Case 0
        dblResult = oTCalc.C2F(dblTemperature)
    Case 1
        dblResult = oTCalc.F2C(dblTemperature)
End Select

    Me.LblResult.Caption = "Result: " & dblResult
    Exit Sub
    Select Case Err.Number
        Case 13
            MsgBox "Enter correct value!", vbInformation, "Error!"
        Case Else
            MsgBox Err.Description, vbExclamation, "Error - " & Err.Number
    End Select
    dblResult = 0
    Resume Exit_CmdCalculate_Click

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Set oTCalc = Nothing
End Sub

Run UserForm (F5) and be happy with your custom control and class.
technotrope 5-Apr-11 16:41pm
Losmac -

Once again, many thanks for your reply. I feel like you're steering me in the right direction.

The reason I started the control in .NET was that I wanted to inherit the picturebox control. I'm trying to replace a 3rd-party (out of business, now) imagemap control we rely heavily upon.

Any way you know of I can test if a mouse-down point falls within a shape defined by X,Y coordinates?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100