Can VB6 compile Excel VBA Code using the Excel object model?

[Dennis]

Can the Excel VBA object model be connected to VB6 so that the VBA code can be
compiled into an exe?

In other words can VBA code such as:
============================================================================
Selection.Sort Key1:=rngsort, Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
or

Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=" & col1, Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:="=" & col4, Operator:=xlAnd
or

With curwk
Set rng = Intersect(.AutoFilter.Range.Offset(1, 0) _
.Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible), _
.AutoFilter.Range.Columns(ii))
End With

etc.
============================================================================
be complied into an exe or a DLL in VB6?

TIA

Dennis

 

[Bob Phillips]

Dennis,

Yes, it can but rather than assuming an Application as you can in Excel, you
need to explicitly reference an Excel application.

With early binding, reference the Excel type library and use

Dim xlApp As Excel.Application

Set xlApp = New Excel.Application

and then precede all objects with xlApp, such

Set xlWB = xlApp.Workbooks.Open Filename:= etc.

If using late binding, use

Set xlApp = CreateObject("Excel.Application")

rest as before.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Dennis,

VB6 can compile exe's, which would typically be used to control Excel, and
can compile ActiveX DLL's, which would typically be under the control of
Excel/VBA. In either case, the user's computer must have Excel installed.
You cannot compile code to manipulate Excel files without Excel. (Well, it
is theoretically possible, but not so in practical terms.)

The first step in VB6 would be to add a reference (from the Project menu,
References item) to the Excel library. Then, you would create a new
instance of the Excel application, using code like the following

' early binding
Dim XL As Excel.Application
Set XL = New Excel.Application

'or, late binding
Dim XL As Object
Set XL = CreateObject("Excel.Application")

In either case, the XL object will serve the same purpose as the Application
object in VBA. You can then prefix all your VBA lines of code with "XL." to
port the VBA code to VB6. E.g.,

XL.Selection.Sort ....
XL.ActiveWorkbook....


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Dennis]

Thank you Chip for the info.

I own Excel 2000 so this is not a problem.

Do you know if the VB6 executable would execute much faster than the VBA code?

My current VBA code takes about 2 hours to go through 39 xls files which have
5000 rows and 36 columns each.

Thanks.

Dennis

 

[Dennis]

Thank you Bob for the VB6 info.

Dennis

 

[Chip Pearson]

Dennis,

You'll get some performance increase using VB6 rather than VBA. How much is
impossible to say without seeing the actual code. A better approach to
solving performance questions is to first examine the fundamental algorithm
being used, regardless of programming language. If the algorithm itself is
unsound, changing languages is of little real use. Your code shouldn't use
any Select or Activate statements, as these cause a lot of processing that
isn't necessary. You should also use With statements, or temporary
variables, whenever possible rather than fully qualifying range specifiers
multiple times. If possible, turn the calculation mode to manual, and turn
off screen updating.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Dennis]

Thanks again Chip.

I currently turn the calculation mode to manual, and turn
off screen updating on all my VBA code.

I'll have to check my code for the Select and Activate statements and replace
them if possible.

How do you replace the selection in the autofilter as in:
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=" & col1, Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:="=" & col4, Operator:=xlAnd
Is the Selection statement unnecessary? These Selection.autofilter statements
plus more are in a big For Loop iteration.

Also in the For loop is
With curwk
Set rng = Intersect(.AutoFilter.Range.Offset(1, 0) _
.Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible), _
.AutoFilter.Range.Columns(ii))
End With
'check for empty cells in rng if so set myErr=1
If rng(1).Value = "" Or rng(1).Value = " " Then myErr = 1
If rng Is Nothing Then myErr = 1
If myErr = 0 Then
If condition1 Then
Maxval = Application.Max(rng)
Else
Minval = Application.Min(rng)
End If

which takes the results of the autofilter and finds the maximum and minimum of
of the visable cells in each of the 39 columns.

I can't see how to not to set the rng for each loop interation and still find
the maximum and minimum for each of the visable cells in the 39 ii columns.

Thanks for your help .

Dennis

 

[Dave Peterson]

I'm not Chip, but one way:

Option Explicit
Sub testme1()

Dim COL1 As Long
Dim COL4 As Long

COL1 = 5
COL4 = 8

With ActiveSheet
.AutoFilterMode = False
With .Range("a:j")
.AutoFilter Field:=1, Criteria1:="=" & COL1
.AutoFilter Field:=4, Criteria1:="=" & COL4
End With
end with

End Sub

(I like to filter my data by columns--and I used A:J in this example.)

And I'm not sure what your variables are when the code runs, but this might give
you some ideas.

Option Explicit
Sub testme2()

Dim curWk As Worksheet
Dim rng As Range
Dim myErr As Long
Dim maxVal As Double
Dim minVal As Double
Dim ii As Long
Dim condition1 As Boolean

With curWk
With .AutoFilter.Range
On Error Resume Next
Set rng = Intersect(.Offset(1, 0) _
.Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible), _
.Columns(ii))
On Error GoTo 0
End With
If rng Is Nothing Then
myErr = 1
Else
'check for empty cells in rng if so set myErr=1
If Trim(rng(1)).Value = "" Then
myErr = 1
Else
If condition1 Then
maxVal = Application.Max(rng)
Else
minVal = Application.Min(rng)
End If
End If
End If
End With

End Sub

 

[Dennis]

Thanks Dave.

Neat code!

My intersect code was from your post to one of my queries 2 years ago. My
"Selection.AutoFilter" code was from using the Microsoft "Record Macro" feature.
I had no idea that this was wasting CPU time unnecessarily.

My columns are numbered from istart to iend, where istart and iend are variable
depending upon where the start and end columns are on the spreadsheet, and are
found from the labels on the first row.

I would have to change .Range("a:j") to .Range(column(istart),column(iend)) ?

What does "On Error GoTo 0" mean? Where is 0. What happens with "On Error
Resume Next" statement above when "On Error GoTo 0" is initiated?

Thanks again.

Dennis

 

[Chip Pearson]

Dennis,

On Error Goto 0 means that error handling is turned off, and any error that
occurs will pause the code in break/debug mode.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

Thanks Dave.

Neat code!

My intersect code was from your post to one of my queries 2 years ago. My
"Selection.AutoFilter" code was from using the Microsoft "Record Macro" feature.
I had no idea that this was wasting CPU time unnecessarily.

My columns are numbered from istart to iend, where istart and iend are variable
depending upon where the start and end columns are on the spreadsheet, and are
found from the labels on the first row.

I would have to change .Range("a:j") to

..Range(column(istart),column(iend)) ?

 

[Dave Peterson]

Just to add to what Chip wrote:

Since you might have filtered using a criteria that returned no visible rows,
that Set statement could blow up. The "on error resume next" means that I
understand that and I'll handle the error myself. After I'm done with the code
that could blow up, I tell excel/vba should go back to worrying about errors.

But I'm testing to see if that Set statement was successful with:
if rng is nothing then...


====
and be careful here:

..Range(column(istart),column(iend))
A typo: column should read columns

and maybe a problem:
..Range(.Columns(iStart), .Columns(iEnd))

That dot in front of .columns means that I want it to belong to the previous
With statement. When you leave that dot off, then the range refers to the
activesheet. And curWk may not be the activesheet when you always run the
macro.

(Even though I didn't set curwk to anything in the code I suggested yesterday!)

 

[Dennis]

Thanks again Dave.

I was unaware of the difference between

..Range(Columns(iStart),Columns(iEnd))

and

..Range(.Columns(iStart), .Columns(iEnd))

However, this is an important difference as you explained below and could cause
computational errors if one isn't careful with this code.

Thanks again for the coding lession.

WRT to "if rng is nothing then...", I had to add the statement

If rng(1).Value = "" Or rng(1).Value = " " Then myErr = 1

or else sometimes when the visable was empty the "if rng is nothing then..."
statement did not work alone. Maybe it had something to do with the Labels
being in the first row and the rng definition being
Set rng = Intersect(.AutoFilter.Range.Offset(1, 0) _
.Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible), _
.AutoFilter.Range.Columns(ii))
in my code

Dennis

 

[Dave Peterson]

I don't see why "if rng is nothing then" would fail.

You already offset the rng by one row (.offset(1,0), then you reduce the size by
one (.rows.count-1).

I'm gonna guess that you've used the variable rng in the same routine and it was
set to an existing (real) range.

I applied an autofilter to a test worksheet manually. Then I filtered on
something that didn't exist (no visible rows, save the header).

This failed:

Option Explicit
Sub testme()

Dim rng As Range
Dim ii As Long

ii = 1
With ActiveSheet
Set rng = .Range("a1")
On Error Resume Next
Set rng = Intersect(.AutoFilter.Range.Offset(1, 0) _
.Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible), _
.AutoFilter.Range.Columns(ii))
On Error GoTo 0
End With

If rng Is Nothing Then
MsgBox "nothing"
Else
MsgBox rng.Address(0, 0)
End If
End Sub

Rng was still set to A1. Not what I wanted.

The bad news is that in your existing code, you may have just masked your
error. Your range may be empty--or it might not be. (depends on what you used
it for previously).

If you set your rng variable to nothing (getting rid of that previous
assignment), I think you'll be ok (and I'd get this to work so I could drop that
"myerr = 1" line).

Option Explicit
Sub testme()

Dim rng As Range
Dim ii As Long

ii = 1
With ActiveSheet
Set rng = Nothing '<--- add this
On Error Resume Next
Set rng = Intersect(.AutoFilter.Range.Offset(1, 0) _
.Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible), _
.AutoFilter.Range.Columns(ii))
On Error GoTo 0
End With

If rng Is Nothing Then
MsgBox "nothing"
Else
MsgBox rng.Address(0, 0)
End If
End Sub

WRT to "if rng is nothing then...", I had to add the statement

If rng(1).Value = "" Or rng(1).Value = " " Then myErr = 1

or else sometimes when the visable was empty the "if rng is nothing then..."
statement did not work alone. Maybe it had something to do with the Labels
being in the first row and the rng definition being
Set rng = Intersect(.AutoFilter.Range.Offset(1, 0) _
.Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible), _
.AutoFilter.Range.Columns(ii))
in my code

Dennis

<<snipped>>

 

[Dennis]

Interesting, you may be right about setting the rng to nothing before beginning
the intersect code.

The reason I put the statement

If rng(1).Value = "" Or rng(1).Value = " " Then myErr = 1

in my code was that when I examined the cases where the Intersect code produced
no visable rows sometimes the row below the label row had blanks or nulls.

However, your point about the possible masking of my errors when the Intersect
produces no visable rows and rng is left in some previous state instead of being
set to nothing is disturbing. Maybe when the Intersect produces no visables then
VBA sets rng(1).value = "" which is not the same as rng = Nothing?

I will try your "Set rng = Nothing" and see if I can find any differences in my
output.

Thanks again for the eye opening possible error.

Dennis

 

[Dave Peterson]

Put a "msgbox rng(1).address" after the set, but before you try to check the
value. And have some test data that will return no visible rows. Were you
surprised at what showed up?

Do the same test and add "set rng = nothing" before the set statement. Did it
blow up real good?

 

[Dennis]

Hi Dave,

I tested the Sub below and this is what I found. When the Intersect is empty
and "On Error Resume Next" is commented out I get the error "No Cells Found" and
the set range code is highlighted.

When "On Error Resume Next" is active and "On Error GoTo 0" is active after the
set rng, I get the error "Run Time Error 91" Object variable or With block
variable not set and the MsgBox rng(1).Address is highlighted in the debug.
When "On Error GoTo 0" is active after MsgBox rng(1).Value statement, I get
the Run Time Error 91 again and the "MsgBox rng(1).Value" statement after the
"End With" is highlighted in the debug.

When the "On Error Resume Next" is active and when I comment out all "On Error
GoTo 0" statements and the "Set rng = Nothing" statement is commented out the
code gives me the MsgBox "nothing" window from the If statement. I get the
msgbox "nothing" even if the previous Set Rng produced visable rows and the
current Set Rng produces no visable rows. Which means it doesn't save the last
Set Rng.

Lastly when I activate the "Set rng = Nothing" statement and the "On Error GoTo
0" are commented out the code flows down to the MsgBox "Nothing" statement.
Whenever the "On Error GoTo 0" is active "Run Time Error 91" pops up when the
MsgBox code is executed.

So I would say, to be extra safe initialize with "Set rng = Nothing" before the
Intersect statement. However, it doesn't seem like you need the "Set rng =
Nothing" statement because with "On Error Resume Next" on the code flows down to
the "If rng Is Nothing Then" statement and rng = Nothing was set to True by the
Intersect statement.

Thanks

Dennis

Sub testIntersect()
Dim rng As Range
Dim ii As Long

ii = 10
With ActiveSheet
'On Error Resume Next
'Set rng = Nothing '<--- add this
Set rng = Intersect(.AutoFilter.Range.Offset(1, 0) _
.Resize(.AutoFilter.Range.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible), _
.AutoFilter.Range.Columns(ii))
'On Error GoTo 0
MsgBox rng(1).Address
MsgBox rng(1).Value
'On Error GoTo 0
End With
'MsgBox rng(1).Value
If rng Is Nothing Then
MsgBox "nothing"
Else
MsgBox rng.Address(0, 0)
End If
End Sub

 

[Dave Peterson]

Interspersed.

Hi Dave,

I tested the Sub below and this is what I found. When the Intersect is empty
and "On Error Resume Next" is commented out I get the error "No Cells Found" and
the set range code is highlighted.

That makes sense to me. And why the error handling needs to be there.

When "On Error Resume Next" is active and "On Error GoTo 0" is active after the
set rng, I get the error "Run Time Error 91" Object variable or With block
variable not set and the MsgBox rng(1).Address is highlighted in the debug.
When "On Error GoTo 0" is active after MsgBox rng(1).Value statement, I get
the Run Time Error 91 again and the "MsgBox rng(1).Value" statement after the
"End With" is highlighted in the debug.

Seems very reasonable. If the range is nothing (hasn't been set to a valid
range, then it can't have a .value property.

When the "On Error Resume Next" is active and when I comment out all "On Error
GoTo 0" statements and the "Set rng = Nothing" statement is commented out the
code gives me the MsgBox "nothing" window from the If statement. I get the
msgbox "nothing" even if the previous Set Rng produced visable rows and the
current Set Rng produces no visable rows. Which means it doesn't save the last
Set Rng.

But even better, you don't get any messages from either of these two lines:
MsgBox rng(1).Address
MsgBox rng(1).Value

You're still in "on error resume next" mode, so since both of these are errors
(with no visible cells), it just skips to the next line.

But there's no error in "if rng is nothing then" line. So it gets executed
correctly.

Lastly when I activate the "Set rng = Nothing" statement and the "On Error GoTo
0" are commented out the code flows down to the MsgBox "Nothing" statement.
Whenever the "On Error GoTo 0" is active "Run Time Error 91" pops up when the
MsgBox code is executed.

I think you'll find that it wasn't the "msgbox nothing" that was causing the
error. I bet it was the top one of this pair:
MsgBox rng(1).Address
MsgBox rng(1).Value

Try stepping through your code using F8's and you'll see exactly where it blew
up.

So I would say, to be extra safe initialize with "Set rng = Nothing" before the
Intersect statement. However, it doesn't seem like you need the "Set rng =
Nothing" statement because with "On Error Resume Next" on the code flows down to
the "If rng Is Nothing Then" statement and rng = Nothing was set to True by the
Intersect statement.

I think you need a couple more tests. If the rng variable held a valid range
before it got to this portion of the code, I think you'll want to use "set rng =
nothing".

On the other hand, if rng were never used, then it starts out being set to
nothing. (But be a little careful with this. Make sure you declare your
variables and you don't have any "rng" variables that are still in scope (like a
global/public variable).




<<snipped>>

 

[Dennis]

I think you need a couple more tests. If the rng variable held a valid range
before it got to this portion of the code, I think you'll want to use "set rng =
nothing".

On the other hand, if rng were never used, then it starts out being set to
nothing. (But be a little careful with this. Make sure you declare your
variables and you don't have any "rng" variables that are still in scope (like a
global/public variable).

Hi Dave,

You are right. If rng was set while in the scope and before the Intersect code
then it would be left at the "before setting" when Intersect was empty and the
code could produce erroneous results.

Thanks again for your help.

Dennis

 

[Dennis]

Chip,

Well I created a VB6 program to run the Excel VBA code with the correct object
references. However, the VB6 code ran very slooowly defeating my purpose.

I described the VB6 slow execution problem in the post entitled "Why Does VB6
execute Excel Worksheet so Slowly?" in this ng.

Dennis