在VBA中使用VS2008自定义控件(NOT VB) [英] Using a VS2008 Custom Control in VBA (NOT VB)

问题描述

我正在尝试在Visual Studio中创建自定义控件以在VBA中使用.我找到了以下链接并尝试遵循它:

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

链接中的所有内容似乎都可以正常使用，直到描述创建一个用于调用和测试DLL的应用程序为止.

在我的VBA(NOT VB)应用程序中，我添加了对该DLL的引用.然后，我创建了一个表单以及一个只显示该表单的子控件.但是，当我尝试运行此VBA应用程序时，在随附的代码片段中的两个声明上收到未定义用户定义的类型"错误.

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

所以，我的问题是:这是否可以在VBA中工作(不是VB)?或者，我需要对自定义控件进行一些特殊处理才能使其在VBA中工作(不是VB).

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.

推荐答案

1)打开MS Excel
2)ALT + F11打开VBA代码编辑器
3)添加新的类(菜单Insert-> Class模块)，并将其名称更改为:ClsCalcTemp
4)复制并粘贴以下代码:

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)现在，插入新的UserForm(菜单Insert-> UserForm)并将其名称更改为:CalcTempFrm
6)插入控件:
-框架/set属性标题:=选择option/，
-ListBox(在他的框架上；这可能是我们的选项组)/set属性名称:= LstOptTemp/，
-标签(位于框架下方)/set属性标题:= Temperature/，
-TextBox(位于Label的右侧)/set属性名称:= TxtTemperature/
-标签(在文本框下方)/set属性名称:= LblResult/
7)复制并粘贴以下代码:

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

运行UserForm(F5)，并对您的自定义控件和类感到满意.

Run UserForm (F5) and be happy with your custom control and class.

这篇关于在VBA中使用VS2008自定义控件(NOT VB)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！