How to invoke let property

[Taurus]

Hello,

I have 2 separate application. One application is running excel report
using VBA. It contain the Let Prop(Key as string, value as variant).
Another application is an exe.
Currently, I would like to write a code in exe to invoke the let prop
in the excel report. Because the let property is the parameter get pass
in order to run the report.

I am able to open the workbook but unable to invoke

Public Property Let Prop(ByRef Key as string, ByRef value as variant)
Select Case Key
Case "apple"
set gen = value
End select
End sub

in the excel workbook. How should I do that?

In the exe the code I have is as following code.
dim oExcel as new excel.application
dim oExcel1 as new excel.application

Set oExcel = VBA.CreateObject("Excel.Application")
oExcel.open<XLS>
set oExcel2 = oExcel.ActiveWorkBook
I would like to invoke the Let Property in the excel from exe. What is
the correct syntax?

Excel2.Prop("Key") = Value

Thank you if anyone could give me some feedback

 

[JE McGimpsey]

Thank you if anyone could give me some feedback

You may have more luck in a non-Macintosh XL newsgroup - Macs can have
only one instance of XL running. There are workarounds for MacXL, post
back if you want them.

Couple of things -

1) You should *never* Dim x as NEW y. Instead declare it as a y, and set
it as a new y:

Dim oExcel As Excel.Application
Set oExcel As New Excel.Application

The reason is that by declaring x as a NEW y, x is not created at
compile time. Instead, x is created at run-time, so

Dim oExcel As New Excel.Application
'
' Code here
'
Set oExcel = VBA.CreateObject("Excel.Application")

is executed ]roughly equivalent to:

Dim oExcel As New Excel.Application
'
' Code here
'
If Not oExcel Is nothing Then
Set oExcel = New Excel.Application
End If
Set oExcel = VBA.CreateObject("Excel.Application")

Worse, this code is executed EVERY time your variable occurs. That
creates a lot of overhead during runtime.

2) The Property Let is invoked by setting the property. If your class
"MyClass" has your code:

Public Property Let Prop(ByRef Key As String, _
ByRef value As Variant)
Select Case Key
Case "apple"
Set gen = value
End Select
End Property

(note "End Property", not "End Sub"). Then you can set Prop with

Dim clsMC1 As MyClass
Set clsMC1 = New MyClass

clsMC1.Prop(Key:="apple") = Range("A1")

the last variable in your property argument list is assigned whatever's
on the right side of the = sign. Since you use

Set gen = Value

Value needs to refer to an object.

You can read more about this in Help ("Calling Property Procedures").

 

[Taurus]

Thank you JE. I have to late bind the excel in order to invoke the Let
Property bag ...