Using a VS2008 Custom Control in VBA (NOT VB)

Question

4.00/5 (1 vote)

See more:

, +

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:

http://msdn.microsoft.com/en-us/library/ms973802.aspx[^]

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()
frmTemperature.show
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.

Posted 10-Mar-11 11:17am

technotrope

Updated 10-Mar-11 14:44pm

Henry Minute

v2

Add a Solution

Comments

Maciej Los 17-Mar-11 17:00pm

Which version of VS2008 do you have: Express, Standard, Pro version?

technotrope 17-Mar-11 17:55pm

Standard

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2011-04-05T09:46:00

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

Exit_CmdCalculate_Click:
    Me.LblResult.Caption = "Result: " & dblResult
    Exit Sub
    
Err_CmdCalculate_Click:
    Select Case Err.Number
        Case 13
            MsgBox "Enter correct value!", vbInformation, "Error!"
            Me.TxtTemperature.SetFocus
            
        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.