Re: Best solution to connect Excel spreadsheet to VB6 module Options?
- From: "Peter T" <peter_t@discussions>
- Date: Wed, 1 Apr 2009 11:07:31 +0100
"Etonian" <eastside88@xxxxxxxxx> wrote in message news:57458607-f892-4aa2-
Hi. The VB6 module will be a stand-alone executable. The module
will take inputs from the spreadsheet where almost all calcs
will occur. I'll arrange for these to come from ranges of cells that
contain 1 or 0. The VB module will fire up transactions via an API
(I'm doing this via VBA now so that part of the code I'm OK with.)
I know in another reply you said something about some API will not work in
VBA, hence the VB, but curiosity which API does not work in VBA.
Up to now, I had been thinking in term of scanning because this
is what I'm doing now and for a couple of good reasons trapping
cell events has not been a viable option for me in Excel.
Why ever not, Excel works very well in this respect.
Here's something to get you started with referencing and trapping your sheet
events in a VB project. Save an Excel file and hardcode its filename in the
following (eventually you won't want to be hard coding the file name and set
the ref by other means).
Start a new exe project, add a form with a button and a class named Class1.
Set a reference to MS Excel x.0 as described before.
Run the form, change cell A1 on Sheet1, look at cell B2.
'''' Form code
Private Declare Function SetWindowLongA Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Const GWL_HWNDPARENT As Long = -8
Private mCls As Class1
Private Sub Command1_Click()
Static n As Long
n = n + 1
mCls.propWS.Range("A5") = n
End Sub
Private Sub Form_Load()
Dim sFile As String
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim hWndXL As Long
sFile = "C:\Documents and Settings\Owner\My Documents\TestEvent.xls"
On Error Resume Next
Set wb = GetObject(sFile)
If wb Is Nothing Then
MsgBox "File does not exist or some other problem to open" & vbCr &
sFile
Unload Me
Else
wb.Parent.Visible = True
wb.Windows(1).Visible = True
wb.Activate
hWndXL = wb.Parent.hwnd ' in xl2000 use API FindowWindow
' attach this form to Excel
SetWindowLongA Me.hwnd, GWL_HWNDPARENT, hWndXL
Set ws = wb.Worksheets("Sheet1")
If Not ws Is Nothing Then
ws.Activate
Set mCls = New Class1
Set mCls.propWS = ws
Else
MsgBox "Sheet1 does not exist"
Unload Me
End If
End If
End Sub
'' Class1
Private WithEvents mWS As Excel.Worksheet
Private mLastPrice As Double
Property Set propWS(ws As Excel.Worksheet)
Set mWS = ws
On Error Resume Next
mLastPrice = mWS.Range("A1").Value
End Property
Property Get propWS() As Excel.Worksheet
Set propWS = mWS
End Property
' get events via the dropdowns
Private Sub mWS_Change(ByVal Target As Excel.Range)
Dim newPrice As Double
Dim s As String
If Target(1).Address = "$A$1" Then
newPrice = Target(1)
Select Case newPrice
Case mLastPrice: s = "unchanged"
Case Is > mLastPrice: s = "up"
Case Else: s = "down"
End Select
mLastPrice = newPrice
Target(1).Offset(, 1) = s
End If
End Sub
You will also probably want to trap the sheet being deleted or its parent
workbook closing so you can release any related references. So you may also
want to trap Excel's application level events, and/or those of the workbook.
Regards,
Peter T
.
- Follow-Ups:
- Prev by Date: Re: IDE colors
- Next by Date: Re: IDE colors
- Previous by thread: Re: How to close a shelled process?
- Next by thread: Re: Best solution to connect Excel spreadsheet to VB6 module Options?
- Index(es):
Relevant Pages
|
