Solved Manipulating Excel From Access (2 Viewers)

Isaac

Lifelong Learner
Local time
Today, 01:28
Joined
Mar 14, 2017
Messages
8,738
Yes, if you find orphaned instances of Excel in Task Manager you can be 99.999999% certain that you have an 'unqualified reference' to an Excel object in your automation code.
Okay I'm curious about this... are you saying that once something is early bound as an Excel item, no matter how it is Set thereafter, but if not qualified all the way back to the Application then it will fire up a new application instance?? or...?
 

cheekybuddha

AWF VIP
Local time
Today, 08:28
Joined
Jul 21, 2014
Messages
2,237
>> But I mean we're talking about someone brand new to VBA... to me it makes more sense to learn the generic thing that I said, and with time learn everything you said <<
Fair enough, apologies if I have confused @GC2010, but I find these kinds of discussions interesting - there is no right or wrong way, just trying to find the way that fits a situation best! 👍

d
 

Isaac

Lifelong Learner
Local time
Today, 01:28
Joined
Mar 14, 2017
Messages
8,738
As functions can do everything that a sub can do ...and return a value, there are developers who only ever write code as functions
Don't disagree. In my own experience there are enough times where I just want to do something and having to mess with returning a value would be unnecessary additional code. That's why I told the OP nothing wrong with it - I was just curious because in OP case, and as confirmed in their reply, none of these things applied....was just curious. I like to hear all of these extra reasons....reminds me of why functions are great! Thanks
 

cheekybuddha

AWF VIP
Local time
Today, 08:28
Joined
Jul 21, 2014
Messages
2,237
>> are you saying that once something is early bound as an Excel item, no matter how it is Set thereafter, but if not qualified all the way back to the Application then it will fire up a new application instance?? or...? <<

Hmmm... I'm not sure that's what I meant, but maybe!!

The Excel object model is a bit of a nightmare because you can access many different classes from the top level even though they are properties/collections of other classes.

So, if you are in Excel, you can stuff like:
Code:
' ...
  Sheets("Sheet1").Range("A1") = "Hello"
  Sheets("Sheet1").Range("A1").Select
  Sheets("Sheet2").Cell(1, 1) = ActiveCell
' ...
No problem.

However, if you do something like the following in Access:
Code:
' ...
  Set xl = CreateObject("Excel.Application")
  Set wb = xl.Workbooks.Open("C:\somefile.xlsx")
  Sheets("Sheet1").Range("A1") = "Hello"        ' Here the Sheets object is unqualified
  Sheets("Sheet1").Range("A1").Select          ' Same here
  Sheets("Sheet2").Cell(1, 1) = ActiveCell     ' Here both Sheets and ActiveCell are unqualified
  wb.Save
' ...
You will get the hanging instance.

You must do:
Code:
' ...
  Set xl = CreateObject("Excel.Application")
  Set wb = xl.Workbooks.Open("C:\somefile.xlsx")
  wb.Worksheets("Sheet1").Range("A1") = "Hello"
  wb.Worksheets("Sheet2").Cell(1, 1) = wb.Worksheets("Sheet1").Range("A1")
  wb.Save
' ...

This is basically a bug which MS couldn't be bothered to fix for many many years. I *think* they have tried to justify it as a feature though I can't imagine what use it would be!

Obviously it's easy to spot in the above examples, but sometimes you can spend ages trying to track down where that unqualified reference is if the code is more complex. Also, the code produced by the macro recorder only exacerbates the issue, since it includes all the UI movements as well.
 

Isaac

Lifelong Learner
Local time
Today, 01:28
Joined
Mar 14, 2017
Messages
8,738
I see. Or create and properly set a worksheet variable (in last example).

I never realized this would cause instances of the application--I thought it just wouldn't work at all! Guess I've never tried it, ha ha. Thanks for the info.

For sure, the macro recorder creates junk code, I call it. Rule of thumb to avoid the tracking down...Declare and Set all variables possible, most notably the application, the workbook, and any worksheets, then utilize them fully
 

isladogs

MVP / VIP
Local time
Today, 08:28
Joined
Jan 14, 2017
Messages
18,186
Don't disagree. In my own experience there are enough times where I just want to do something and having to mess with returning a value would be unnecessary additional code. That's why I told the OP nothing wrong with it - I was just curious because in OP case, and as confirmed in their reply, none of these things applied....was just curious. I like to hear all of these extra reasons....reminds me of why functions are great! Thanks
Sorry, but the point is that whilst functions are required if you need to return a value, they also work perfectly well where you don't need a value.
Whilst I still tend to use subs where no value is required, that is because I learned the 'same rule' you described.
But its an unnecessary distinction.
 

cheekybuddha

AWF VIP
Local time
Today, 08:28
Joined
Jul 21, 2014
Messages
2,237
Isaac,

>> Rule of thumb to avoid the tracking down...Declare and Set all variables possible, most notably the application, the workbook, and any worksheets, then utilize them fully <<

Really excellent process to follow 👍

But believe me, I try and follow a similar process and I still make the error sometimes, even when I'm fully aware of the issue!! 😖
 

Isaac

Lifelong Learner
Local time
Today, 01:28
Joined
Mar 14, 2017
Messages
8,738
>>Sorry, but the point is that whilst functions are required if you need to return a value, they also work perfectly well where you don't need a value. <<

True - I'm not disagreeing-don't be sorry. I misspoke on having to code to return a value, and actually forgot for a moment that functions dont' even have to. As you can tell my ratio of Subs to Functions is like 10 to 2 :)
 

Minty

AWF VIP
Local time
Today, 08:28
Joined
Jul 26, 2013
Messages
10,355
Sub is so much shorter to type than Function, my fingers are getting tired in old age... :whistle:

In all seriousness, I still use both pretty randomly just out of habit.
Generally Subs within form modules and Functions elsewhere but definitely no rhyme or reason unless I'm actually writing a function. Just laziness or familiarity I suspect.
 

Micron

AWF VIP
Local time
Today, 04:28
Joined
Oct 20, 2018
Messages
3,476
Let's not forget that you can't call a sub from the ribbon (or old toolbars) or use it as a procedure for a form or report event. You must use a function for these. I don't know if you can call a sub from a query, but if you can, it would be pointless since nothing will be returned as criteria or a calculated field value. I mention that because in spite of all the reasons I see here for using one or the other, this wasn't mentioned - not that I noticed anyway.
 

Users who are viewing this thread

Top Bottom