用户形式的vba对象引用不能正常工作 [英] vba object reference from userform not working
问题描述
我们已经做了大量的研究，为我们的一些较少的技术顾问找到一些数据相关性，这个VBA工具将是他们给出的一个快速参考客户实时预测。这个问题与这个问题无关。
我创建了一个类来包含值，然后计划是从VBA userform引用类。当我引用类时，我没有得到我已经存储的值，但是我得到了属性的名称。
Private Sub ddMonthOfUse_Change（）
Dim mv As String
设置chillWater =新的clsMonth
与chillWater
.Janurary = 0.7136
.Feburary = 0.6755
.March = 0.6528
.April = 0.7773
.May = 0.8213
.June = 0.8715
.July = 0.9
.August = 1.0243
.September = 1.0516
.October = 0.8514
.November = 0.7095
。十二月= 0.6994
结束
设置DX =新的clsMonth
与DX
.Janurary = 0.5777
.Feburary = 0.5536
.March = 0.5166
.April = 0.6112
.May = 0.7035
.June = 0.75
.July = 0.8
.August = 0.8345
。 9月= 0.9333
.October = 0.6865
.November = 0.5976
.December = 0.4907
结束
MsgBox chillWater.m onth
End Sub
在课程模块中我有这个
选项显式
'属性decleration
公共Janurary作为单个
公共二元单
公开三月单价
公开四月单价
公开五月单价
公开六月单价
公开七月单价
公开八月单一
公开九月单一
公共十月单价
公开十一月单一
公共十二月单一
公共冷水作为clsMonth，DX As clsMonth
公共财产获取月（）As String
月= Main.ddMonthOfUse.value
结束属性
而不是消息框返回值，如果用户选择月3月的消息框说三月。例如，如果我替换下拉列表的引用，并且只要输入March，我就得到这个值。
我无法理解我的生活。请记住，我不是一个老练的程序员，我对这个主题有非常初步的了解，所以我可以在这里完全离开。另外我应该提到消息框不是最终使用的信息，我只是测试看看我是否正确地调用它。
@ Mat'sMug当用户形式的组合框被选中（说用户选择May），我想要消息框显示chillwater的值。可以
删除所有这些，它们有毒：
公共冷水作为clsMonth，DX As clsMonth
公开属性Get month（）As String
month = Main.ddMonthOfUse.value
结束属性
您需要公开一个函数，它需要一个月的名字并返回相应的字段值。
一种方法可以是利用 CallByName 并执行如下操作：
公共函数ValueFor（ByVal monthName As String）As Single
On Error GoTo CleanFail
Dim instance As Object
Set instance = Me'CallByName can'直接拿我
Dim result As Single
result = CallByName（instance，monthName，VbGet）
CleanExit：
ValueFor = result
退出函数
CleanFail：
result = 0
恢复CleanExit
结束函数
注意 V bGet 参数 - 它将使用公共字段（我刚刚测试它），但理想情况下，您将封装这些值，并将其公开为属性而不是我喜欢这样封装我的东西：
Option Explicit
私有类型TMonth'T+ ClassName，只是按照惯例
January作为单个
二月作为单个
'...
十二月作为单个
结束类型
私人这个作为TMonth唯一需要的私人领域！
'现在暴露每个成员的属性访问器：
公共属性获取1月（）作为单个
1月= this.January
结束属性
公共财产让1月（ByVal值为单）
this.January = value
结束属性
'...
然后你的表单代码可以这样做：
设置chillWater =新的clsMonth
使用chillWater
.Janurary = 0.7136
.Feburary = 0.6755
。 = 0.6528
.April = 0.7773
.May = 0.8213
.June = 0.8715
.July = 0.9
.August = 1.0243
.September = 1.0516
.October = 0.8514
.November = 0.7095
.December = 0.6994
MsgBoxValue for& ddMonthOfUse.value& ：& 。$ v $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ b b b b b b b b b b b b b b b b bI am working and have hit a bit of a roadblock. For one I am not the strongest programmer, I was just given this task but have hit a roadblock (I think its syntax but not sure).
We have done a lot of research to find some data correlations for some of our less technical consultants, and this VBA tool is going to be a quick reference for them to give customers real time projections. The subject is irrelevant to the question.
I have created a class to contain the values, and then the plan was to reference the class from the VBA userform. When I reference the class, I don't get the value that I have stored, but I get the name of the property.
Private Sub ddMonthOfUse_Change()
Dim mv As String
Set chillWater = New clsMonth
With chillWater
.Janurary = 0.7136
.Feburary = 0.6755
.March = 0.6528
.April = 0.7773
.May = 0.8213
.June = 0.8715
.July = 0.9
.August = 1.0243
.September = 1.0516
.October = 0.8514
.November = 0.7095
.December = 0.6994
End With
Set DX = New clsMonth
With DX
.Janurary = 0.5777
.Feburary = 0.5536
.March = 0.5166
.April = 0.6112
.May = 0.7035
.June = 0.75
.July = 0.8
.August = 0.8345
.September = 0.9333
.October = 0.6865
.November = 0.5976
.December = 0.4907
End With
MsgBox chillWater.month
End Sub
In the class module I have this
Option Explicit
'property decleration
Public Janurary As Single
Public Feburary As Single
Public March As Single
Public April As Single
Public May As Single
Public June As Single
Public July As Single
Public August As Single
Public September As Single
Public October As Single
Public November As Single
Public December As Single
Public chillWater As clsMonth, DX As clsMonth
Public Property Get month() As String
month = Main.ddMonthOfUse.value
End Property
Instead of the message box returning the value, if the user selects month March the message box says "March." If, for example, I replace the reference to the dropdown and just type March, I get the value.
I can't figure this out for the life of me. Please keep in mind I am not a veteran programmer at all, I have very rudimentary knowledge of the subject so I could be completely off here. Also I should mention that the message box is not the final use of the information, I was just testing to see if I was calling it correctly.
解决方案
@Mat'sMug when the combobox in the userform is selected (say the user chooses May), I want the message box to display the value of chillwater.May
Okay, got it. Remove all these, they're toxic:
Public chillWater As clsMonth, DX As clsMonth
Public Property Get month() As String
month = Main.ddMonthOfUse.value
End Property
You need to expose a Function that takes a month's name and returns the corresponding field's value.
One way could be to leverage CallByName and do something like this:
Public Function ValueFor(ByVal monthName As String) As Single
On Error GoTo CleanFail
Dim instance As Object
Set instance = Me 'CallByName can't take "Me" directly
Dim result As Single
result = CallByName(instance, monthName, VbGet)
CleanExit:
ValueFor = result
Exit Function
CleanFail:
result = 0
Resume CleanExit
End Function
Notice the VbGet parameter - it's going to work with a public field (I just tested it), but ideally you would encapsulate these values and expose them as properties instead. I like encapsulating my stuff like this:
Option Explicit
Private Type TMonth '"T" + ClassName, just by convention
January As Single
February As Single
'...
December As Single
End Type
Private this As TMonth 'the only private field you need!
'now expose a property accessor for each member:
Public Property Get January() As Single
January = this.January
End Property
Public Property Let January(ByVal value As Single)
this.January = value
End Property
'...
And then your form code can do this:
Set chillWater = New clsMonth
With chillWater
.Janurary = 0.7136
.Feburary = 0.6755
.March = 0.6528
.April = 0.7773
.May = 0.8213
.June = 0.8715
.July = 0.9
.August = 1.0243
.September = 1.0516
.October = 0.8514
.November = 0.7095
.December = 0.6994
MsgBox "Value for " & ddMonthOfUse.value & ": " & .ValueFor(ddMonthOfUse.Value)
End With
这篇关于用户形式的vba对象引用不能正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
