KnowBrainer Speech Recognition
Decrease font size
Increase font size
Topic Title: Excel Fill Color Macro Problem
Topic Summary: Error message returned concerning ActiveX Accessibility problem
Created On: 09/15/2010 04:18 PM
Status: Post and Reply
Linear : Threading : Single : Branch
 Excel Fill Color Macro Problem   - Ron Len - 09/15/2010 04:18 PM  
 Excel Fill Color Macro Problem   - R. Wilke - 09/15/2010 05:10 PM  
 Excel Fill Color Macro Problem   - Ron Len - 09/15/2010 05:52 PM  
 Excel Fill Color Macro Problem   - R. Wilke - 09/15/2010 06:11 PM  
 Excel Fill Color Macro Problem   - Ron Len - 09/15/2010 07:28 PM  
 Excel Fill Color Macro Problem   - R. Wilke - 09/15/2010 07:36 PM  
 Excel Fill Color Macro Problem   - Ron Len - 09/15/2010 08:23 PM  
 Excel Fill Color Macro Problem   - monkey8 - 09/18/2010 07:09 PM  
 Excel Fill Color Macro Problem   - Ron Len - 09/18/2010 07:32 PM  
 Excel Fill Color Macro Problem   - monkey8 - 09/18/2010 08:10 PM  
 Excel Fill Color Macro Problem   - Ron Len - 09/18/2010 08:34 PM  
 Excel Fill Color Macro Problem   - Ron Len - 09/18/2010 09:42 PM  
 Excel Fill Color Macro Problem   - Larry Allen - 09/18/2010 11:12 PM  
 Excel Fill Color Macro Problem   - Ron Len - 09/19/2010 12:03 AM  
 Excel Fill Color Macro Problem   - DrKnow - 06/15/2020 09:37 AM  
 Excel Fill Color Macro Problem   - DrKnow - 06/15/2020 09:50 AM  
 Excel Fill Color Macro Problem   - DrKnow - 08/31/2020 05:35 AM  
 Excel Fill Color Macro Problem   - speechpro - 06/15/2020 10:26 AM  
 Excel Fill Color Macro Problem   - kkkwj - 06/17/2020 01:15 AM  
Keyword
 09/15/2010 04:18 PM
User is offline View Users Profile Print this message


Ron Len
Member

Posts: 116
Joined: 10/02/2006

Below is a script that Larry Allen suggested for adding the fill to a cell in Excel. I did as he suggested, using the reference Object Library for Excel, which was 12.0.

I got the script to work for several different colors and then I closed down the worksheet at the end of the session and went back to it the next day. However, when using the same command the next day and since, I receive an error message that mentions that there is a problem at line 13 and is in regards to ActiveX Accessibility.

I looked at my script and nothing changed from the original script that I was successfully using.

Not being a formally trained programmer, I would like to know what I am missing and where the problem is? Why was I able to originally use it and now all it does is lock up the Excel program?

The code that I'm using, with the information statement for my own purposes, is as follows:

' Color numbers are from: http://www.mvps.org/dmcritchie/excel/colors.htm
' Color 10 -- lighter green
' color 3 -- red
' color 6 -- yellow
' color 2 -- white
' color 1 -- Black
' color 15 -- light gray
' color 37 -- light blue
' color 44 -- light orange
Sub Main
Dim objExcel As Object
Set objExcel = GetObject(, "Excel.Application"
'
With objExcel.Selection.Interior
        .ColorIndex = 50 ' this is the color
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
End With

End Sub

______________________________________________

Thanks for any help that you can offer!

Ron

 

 09/15/2010 05:10 PM
User is offline View Users Profile Print this message

Author Icon
R. Wilke
Top-Tier Member

Posts: 7242
Joined: 03/04/2007

Ron, 

I think it might help if you quoted and pointed to the code as contained in line 13. 

Just a general hint for anyone who might be interested, as I have discovered by chance, in the version 11 help file, the entire list of error codes being thrown while executing scripts is listed. You can find it by searching for "error list". This is the list:

ErrorDescription
10000 Execution interrupted.
10001 Out of memory.
10008 Invalid '#Uses "module" comment.
10009 Invalid '#Uses module dependency.
10010 Macro is already running.
10011 Can't allocate memory to macro/module.
10012 Macro/module has syntax errors.
10013 Macro/module does not exist.
10014 Another macro is paused and can't continue at this time.
10017 No macro is currently active.
10018 Sub/Function does not exist.
10019 Wrong number of parameters.
10021 Can't allocate large array.
10022 Array is not dimensioned.
10023 Array index out of range.
10024 Array lower bound is larger than upper bound.
10025 Array has a different number of indexes.
10030 User dialog has not been defined.
10031 User pressed cancel.
10032 User dialog item id is out of range.
10033 No UserDialog is currently displayed.
10034 Current UserDialog is inaccessible.
10035 Wrong with, don't GoTo into or out of With blocks.
10040 Module could not be loaded.
10041 Function not found in module.
10048 File not opened with read access.
10049 File not opened with write access.
10050 Record length exceeded.
10051 Could not open file.
10052 File is not open.
10053 Attempt to read past end-of-file.
10054 Expecting a stream number in the range 1 to 511.
10055 Input does not match var type.
10056 Expecting a length in the range 1 to 32767.
10057 Stream number is already open.
10058 File opened in the wrong mode for this operation.
10059 Error occurred during file operation.
10060 Expression has an invalid floating point operation.
10061 Divide by zero.
10062 Overflow.
10063 Expression underflowed minimum representation.
10064 Expression loss of precision in representation.
10069 String value is not a valid number.
10071 Resume can only be used in an On Error handler.
10075 Null value can't be used here.
10080 Type mismatch.
10081 Type mismatch for parameter #1.
10082 Type mismatch for parameter #2.
10083 Type mismatch for parameter #3.
10084 Type mismatch for parameter #4.
10085 Type mismatch for parameter #5.
10086 Type mismatch for parameter #6.
10087 Type mismatch for parameter #7.
10088 Type mismatch for parameter #8.
10089 Type mismatch for parameter #9.
10090 OLE Automation error.
10091 OLE Automation: no such property or method.
10092 OLE Automation: server cannot create object.
10093 OLE Automation: server cannot load file.
10094 OLE Automation: Object var is 'Nothing'.
10095 OLE Automation: server could not be found.
10096 OLE Automation: no object currently active.
10097 OLE Automation: wrong number of parameters.
10098 OLE Automation: bad index.
10099 OLE Automation: no such named parameter.
10100 Directory could not be found.
10101 File could not be killed.
10102 Directory could not be created.
10103 File could not be renamed.
10104 Directory could not be removed.
10105 Drive not found.
10106 Source file could not be opened.
10107 Destination file could not be created.
10108 Source file could not be completely read.
10109 Destination file could not be completely written.
10110 Missing close brace '}'.
10111 Invalid key name.
10112 Missing close paren ''.
10113 Missing close bracket ']'.
10114 Missing comma ','.
10115 Missing semi-colon ';'.
10116 SendKeys couldn't install the Windows journal playback hook.
10119 String too long (too many keys).
10120 Window could not be found.
10130 DDE is not available.
10131 Too many simultaneous DDE conversations.
10132 Invalid channel number.
10133 DDE operation did not complete in time.
10134 DDE server died.
10135 DDE operation failed.
10140 Can't access the clipboard.
10150 Window style must be in the range from 1 to 9.
10151 Shell failed.
10160 Declare is not implemented.
10200 Basic is halted due to an unrecoverable error condition.
10201 Basic is busy and can't provide the requested service.
10202 Basic call failed.
10203 Handler property: prototype specification is invalid.
10204 Handler is already in use.



-------------------------



No need to buy if all you want to do is try ...

DragonCapture KB Download (Latest)
DragonCapture Homepage

 09/15/2010 05:52 PM
User is offline View Users Profile Print this message


Ron Len
Member

Posts: 116
Joined: 10/02/2006

Rudiger,

 What you see above in my post is the sum and substance of the entire script. There is nothing more to it. The original script that Larry provided consisted of everything below my comment lines. That was it in a nutshell. I used it and made what I assume to be the proper reference to the object library for Excel 12.0. After that, I was off and running for that one session and everything functioned fine. Now, not so much.

 BTW, I am not using version 11. I am stuck in the dark ages with 10.1 Professional.

Thanks -- Ron

 

 09/15/2010 06:11 PM
User is offline View Users Profile Print this message

Author Icon
R. Wilke
Top-Tier Member

Posts: 7242
Joined: 03/04/2007

Ron,

I see. However, when referring to a particular line in the code, this is where the error is. You can identify the line when editing the script and looking at the status bar, right hand side, which is where the lines are numbered.

Rüdiger

 



-------------------------



No need to buy if all you want to do is try ...

DragonCapture KB Download (Latest)
DragonCapture Homepage

 09/15/2010 07:28 PM
User is offline View Users Profile Print this message


Ron Len
Member

Posts: 116
Joined: 10/02/2006

Rudiger,

My apologies about something. My eyes are not what they used to be many years ago and I was looking at the message stating that it was "line 13" when it actually was "line 15" of the script.

When I went to the Excel spreadsheet to purposely get the error message, I could not get it to appear when I said the command "color cell green". I had another spreadsheet open at the time and no matter what I said (and I used other of the like commands), the error message would not appear. However, when I closed down the other spreadsheet and then said the command, the error message appeared. This is what it basically said under the MyCommands error message:

My Command: color cell green
Line 15
Position 0
Description: (10094) ActiveX Automation: Object var is 'Nothing'

Referring to the Command Browser and the line indicator, "line 15" would be:

ColorIndex = 50 ' this is the color

Hope this helps?

Thanks -- Ron

 09/15/2010 07:36 PM
User is offline View Users Profile Print this message

Author Icon
R. Wilke
Top-Tier Member

Posts: 7242
Joined: 03/04/2007

Ron,

I am not sure, but you might try

ColorIndex="50"

instead.

Rüdiger

 



-------------------------



No need to buy if all you want to do is try ...

DragonCapture KB Download (Latest)
DragonCapture Homepage

 09/15/2010 08:23 PM
User is offline View Users Profile Print this message


Ron Len
Member

Posts: 116
Joined: 10/02/2006

Rudiger,

Sorry to report that the suggested change produces the same error message, when the command is spoken.

Thanks for the suggestion, however.

 Ron

 

 09/18/2010 07:09 PM
User is offline View Users Profile Print this message

Author Icon
monkey8
Top-Tier Member

Posts: 3574
Joined: 01/14/2008

Ron

I have just tried this script and it works fine for me but that is using Excel object library version 14 as opposed to 12.  My guess is that you have a hidden character somewhere in your script (line 12 is my guess).  Just recreate it (don't copy it) and see what happens.

Lindsay

-------------------------

 09/18/2010 07:32 PM
User is offline View Users Profile Print this message


Ron Len
Member

Posts: 116
Joined: 10/02/2006

Lindsay,

Thank you for taking a look at the script. If you do not mind, can you copy "Line 12" in your script so that I can compare it. I will re-create it per your instructions, but I would just like to see what your line of code says.

Thanks -- Ron
 09/18/2010 08:10 PM
User is offline View Users Profile Print this message

Author Icon
monkey8
Top-Tier Member

Posts: 3574
Joined: 01/14/2008

Ron you may not visibly see any difference but I recommend recreating the script line by line to avoid any hidden characters.  Lindsay


-------------------------

 09/18/2010 08:34 PM
User is offline View Users Profile Print this message


Ron Len
Member

Posts: 116
Joined: 10/02/2006

Lindsay,

I will rewrite the whole script as you mentioned. In the meantime, here is what I have experienced after rewriting line 15:

Status report:

When I rewrote line 15 and then tried the script, I had two worksheets open. The worksheet that I invoked the command in did not color a cell. However, when I went to the other worksheet, a cell was highlighted in the color green. Then, I closed down. Excel and restarted it and then used the macro. The error message was returned upon my utterance of the command.

With one worksheet open, the script would not work. It returned an error message that there is a problem on "line 15".

So, I do not know what is going on here.

Anyway, I will go ahead and rewrite every line and see what happens. Thanks for your efforts.

Ron

 09/18/2010 09:42 PM
User is offline View Users Profile Print this message


Ron Len
Member

Posts: 116
Joined: 10/02/2006

Additional information:

I can close down Excel and then open it again and then use the command on a single worksheet. It immediately occurs is that the error message is displayed.. After clicking on the error message, when I use the command, it will work within that single worksheet. However, if I close down Excel and repeat the process, I get the same error message and then can only use the command after closing the error message. The problem is that it cannot be done by voice. I have to have my wife click on it before I can proceed. This is problematic as you can imagine. I have almost succeeded my limit of her good graces. <grin>

Also, I did rewrite the macro, letter for letter and for some reason, Dragon does not like the line:

Set objExcel = GetObject(, “Excel.Application"

It is highlighted in red and I have compared it to your script and it appears that everything is in order. So, I don't know what is going on.

The macro that I referenced in the first paragraph is from the original effort and none of it has been changed or rewritten. It works, albeit, under the conditions outlined above.

I am using Excel 2007 and XP Service Pack 3 Operating System, along with Professional 10.1 DNS.

Thanks for your help, Lindsay.

Ron

 09/18/2010 11:12 PM
User is offline View Users Profile Print this message


Larry Allen
Top-Tier Member

Posts: 461
Joined: 09/04/2007

If I don't have any cell currently selected and in focus, then this problem occurs.  For instance, if the focus is on the formula line, this will always happen with this macro.

The line indicated is the first line where the "Selection" is actually used, and if there is no cell selected AND in focus, then there is no Selection object and it fails.

 



-------------------------

Larry Allen
http://www.pcspeak.com

 09/19/2010 12:03 AM
User is offline View Users Profile Print this message


Ron Len
Member

Posts: 116
Joined: 10/02/2006

Larry,

I think I am understanding what you say? However, when I went to Excel and used the macro, it worked fine for me the first couple of instances. I then closed down Excel. Another worksheet (only one) was opened up and highlighted "cell A2" and invoked the macro. I then got the error message, which then was clicked out of. The macro then would work. At that point, as a test, I then closed down Excel and restarted it. I again went to a cell that was highlighted and focus definitely was indicated as being on the cell. The macro name was again spoken and it failed once more.

And, after reading your comments, when I went to a worksheet, I looked in the box that indicated my location on the spreadsheet. At no time was I on anything other than a cell when the command was spoken.

When it works, it works well, but that is the problem. I never know when it is going to work and where. The strangest part of this is the instance when I was on one worksheet, spoke the command and nothing appeared, but when I went to a different worksheet, cells were highlighted. Strange phenomena!

So, that is it so far. Any idea of what is going on here?

Thank you for your help.

Ron

 06/15/2020 09:37 AM
User is offline View Users Profile Print this message

Author Icon
DrKnow
New Member

Posts: 7
Joined: 10/22/2014

I made slight amendments to the suggested script and this works perfectly for me using Microsoft Excel 2016:

 

' Color numbers are from: http://www.mvps.org/dmcritchie/excel/colors.htm

' Color 10 -- lighter green

' color 3 -- red

' color 6 -- yellow

' color 2 -- white

' color 1 -- Black

' color 15 -- light gray

' color 37 -- light blue

' color 44 -- light orange

Sub Main

Dim objExcel As Object

Set objExcel = GetObject(, "Excel.Application")

With objExcel.Selection.Interior

        .ColorIndex = 6 ' this is the color

End With

End Sub



 06/15/2020 09:50 AM
User is offline View Users Profile Print this message

Author Icon
DrKnow
New Member

Posts: 7
Joined: 10/22/2014

Just to add to my above post, the NO FILL command is outlined below which works for me on Microsoft excel 2016:

Sub Main


Dim objExcel As Object
Set objExcel = GetObject(, "Excel.Application")

With objExcel.Selection.Interior
.ColorIndex = 0 ' this is the number for NO FILL


End With

End Sub



 08/31/2020 05:35 AM
User is offline View Users Profile Print this message

Author Icon
DrKnow
New Member

Posts: 7
Joined: 10/22/2014

As well as the above solutions, you can also set your own RGB colours (custom colours).

You need to establish what RGB colour you wish to use. For instance, in the following example, I'm using an RGB colour set (230,184,183) which is a light red colour:

 

Sub Main

Dim objExcel As Object

Set objExcel = GetObject(, "Excel.Application")

With objExcel.Selection.Interior

        .Color = RGB(230,184,183) ' this is the color

End With

End Sub

 



 06/15/2020 10:26 AM
User is offline View Users Profile Print this message


speechpro
Power Member

Posts: 72
Joined: 11/09/2006

Do you have the constants Pattern and PatternColorIndex defined? 

Try 1 and -4105, respectively, if that works, look up the options either in the Excel VBA IDE, or online...

 06/17/2020 01:15 AM
User is offline View Users Profile Print this message

Author Icon
kkkwj
Top-Tier Member

Posts: 497
Joined: 11/05/2015

1) For sure, you should have a trailing parenthesis on your "Excel.Application" line, like so:

 

 

Set objExcel = GetObject(, "Excel.Application")

 

 

2) You must be sure to make Excel lose the focus at least once after launching it by activating some other window. Losing the focus will force Excel (actually, force Windows) to register Excel in the ROT (running object table) where the "GetObject(...)" can fetch it. If Excel has not lost the focus once, Excel won't be in the ROT, and GetObject won't return a valid Excel object, and your macro will fail (probably with some weird error message).

 

Note that the FIRST instance of Excel to lose focus goes into the ROT FIRST, and GetObject fetches the FIRST instance from the ROT. Thus, if you have two Excel instances and the second instance is active, your macro will fetch the first (inactive) instance from the ROT and try to change the color of a cell in the first instance. But because the first instance is not active, it will not have an active selected cell/range, and your macro will fail.

 

3) For sure, you must properly release your objExcel at the end of the macro, like so:

 

Set objExcel = Nothing

 

Otherwise, Dragon could easily hang on to the old objExcel object and get confused. Worse yet, if Dragon hangs on to the object then Windows will NOT release Excel properly when you quit and restart Excel. You will see Excel disappear from the screen and task bar, but the old instance of Excel - now a zombie instance - will still be the FIRST excel registered in the ROT, and will be returned on your next macro run. Then your macro will be setting colors in a zombie instance that is not visible. (And as you know from 2 above, if the zombie instance is not the active instance, it has no active selection and your macro will fail.)

 

Most of the Office apps are singleton apps (a single instance manages multiple separate visible windows - Word, Excel, etc.). But Access uses a separate instance for every database. Thus, multiple Word/Excel instances in the ROT is normally not an issue; Access is more problematic because it is easier to create multiple instances.

 

 

Having said all that, here's a macro I made up that is more robust. It has the closing parenthesis, properly releases Excel if nothing goes wrong, and protects against cases where the instance has no active selection.  I could only reach back to the Office 14.0 libraries, and you're on Excel 12.0, so maybe my script won't work for you. But I think it should because it does nothing fancy.

 

The Pattern* settings control the color and pattern of the foreground dots that make up the pattern. The Color/Tint settings control the background color of the cells. I used a light tint (0 is dark, 1 is totally light) to show the background color through the pattern dots. On my machine, the cell is light pink with dark blue grid lines.

 

 

Sub Main

' right-click, and choose Reference to add Microsoft Office 14.0 or 16.0 library

' or maybe Excel 12.0, if it has these functions in it

Dim objExcel As Object

Set objExcel = GetObject(, "Excel.Application")

 

 

If objExcel is Nothing Then

    MsgBox "Excel instance not found."

    Exit Sub

End If

 

 

If objExcel.Visible = False Then

    MsgBox "Zombie instance found!."

    Set objExcel = Nothing

    Exit Sub

End If

 

 

If TypeName(objExcel.Selection) <> "Range" Then

    MsgBox "Select one or more cells before running this macro."

    Set objExcel = Nothing

    Exit Sub

End If

 

 

With objExcel.Selection.Interior

    .Pattern = xlGrid

    .PatternColorIndex = 5 'color of the dots for the pattern

    .ColorIndex = 30       'background color under the pattern

    .TintAndShade = 0.8    'make it light enough to see

End With

Set objExcel = Nothing

End Sub

 

 

This morning I added extra code to test for No Excel and Zombie Excel instances. I hope this helps, even though it is probably more than you wanted to know. :-) Good luck!


-------------------------

Win10/x64, AMD Ryzen 7 3700X, 64GB RAM, Dragon 15.3, SP 6 PRO, SpeechStart, Office 365, KB 2017, Dragon Capture, Samson Meteor USB Desk Mic, Klim and JUKSTG earbuds with microphones



Statistics
31794 users are registered to the KnowBrainer Speech Recognition forum.
There are currently 1 users logged in.
The most users ever online was 12124 on 09/09/2020 at 04:59 AM.
There are currently 645 guests browsing this forum, which makes a total of 646 users using this forum.

FuseTalk Standard Edition v4.0 - © 1999-2020 FuseTalk™ Inc. All rights reserved.