question

robertvandevelde-7036 avatar image
0 Votes"
robertvandevelde-7036 asked robertvandevelde-7036 answered

Excel.Range in Project VBA

I need to use a Range variable in a Project VBA sub. I have opened Excel in another sub and set a variable to hold Excel.Application. I call a subroutine to use that instance of Excel. The Dims raise a VBA err # 438. How do I declare the Range variable to use it in the sub?

Private Sub Example(ByRef xlAppLcl As Object,
ByRef xlBookLcl As Object,

ByRef xlSheetLcl As Object,
ByRef ProcErrFlgLcl As Boolean,

ByRef ProcErrMsgLcl As String)

-->Dim xlRow As Excel.Range [I also tried xlAppLcl.Range]
-->Dim xlCol As Excel.Range

Set xlAppLcl.xlRow = xlAppLcl.ActiveCell

End Sub

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TvanStiphout avatar image
0 Votes"
TvanStiphout answered

I would highly recommend not mixing early binding and late binding. Let's use early binding for everything.
So set a reference to Microsoft Excel <version>, and replace all Objects to their proper data types Excel.Xxx.
In the preceding sub let's get rid of CreateObject in favor of New.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

robertvandevelde-7036 avatar image
0 Votes"
robertvandevelde-7036 answered

I'm unable to exclusively use early binding. The VBA code goes to users who might not have the reference checked off. I have to develop using early binding and then switch to late for distribution of the module.

Btw, I found that by changing the Dim from xlRow As Range to xlRow As Object the code worked. I'm not sure why, but maybe you or one of the other VBA gurus (like John or Rod) would know the answer.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.