VBA was working for a while, then error 91

Joe Dunfee · March 2017

I had my Excel to BricsCAD VBA program working fine. I was just adding to the list of model#s and sizes, when my script suddenly stopped working.

I get the error "Run-time error '91':
Object variable or With block variable not set
It is on the line,
acadDoc.SendCommand (Chr(27) & Chr(27)) _'Send Esc keys to cancel any running command in BricsCAD_

Note that this line is before any other script commands. It would be the first attempt to send anything to BricsCAD. It is proceeded by the following,
On Error Resume Next
Set acadDoc = acadApp.ActiveDocument
If acadDoc Is Nothing Then
Set acadDoc = acadApp.Documents.Add
acadApp.Visible = True
End If
On Error GoTo 0

So, if the above code failed, then that would have caused the error. [for some reason I cannot get the forum to format the above as code, though other one-line efforts did work]

If I remove the On Error statements, and run the routine with BricsCAD open, but no document open, or if there is a document open, I get the error,
"Run-Time error, No Document" on this line
Set acadDoc = acadApp.ActiveDocument

Note that I have been getting instances earlier in the day, when I would get another instances of BricsCAD being started by the routine, which it shouldn't do. Also, BricsCAD itself has crashed at least 5 times earlier today.

I rebooted, but the routine still generates the same error.

Note that I had recently saved two versions of the spreadsheet, designed for two different product families. Both have stopped working. So, if I somehow accidentally messed up the code, it is unlikely that I did it to both of the VBA programs.

Gremlins?

Attached is a copy of my spreadsheet with the VBA macro. I somewhat simplified the data in the spreadsheet to make it easier to follow, and to remove reference to blocks that are inserted from my hard drive.

In actual use, I normally was having BricsCAD open, and a new black document open. Then in Excel, I would place my cursor in one of the cells in any of the data rows, and press the button "Send Script". This would start the VBA program and draw the objects.

I first attempted to upload the Excel .xlsm file, but the forum does not allow that. I next tried zipping the .xlsm file and that was successful. I wish the forum would let you know at some point in the upload process what file types are acceptable. It is done by the trial-and-error method as it is now.

-Joe

terry priest · March 2017

try moving the bricscad library reference(s) as high as it will go in the list and recompile.

Joe Dunfee · March 2017

Since I am using the VBA inside Excel, I don't think there is a separate compiling step. So, after moving the two BricsCAD references as high as it will go, just run it as normal.

It is interesting that I got a different error. It was in the portion which I got from an example on this forum. The part that checks if BricsCAD is running and if it has a document open.

Then, this if-then statement fails and brings up the message box, "Autocad did not start"
It does this whether I have BricsCAD open or not when I run the routine. I also tried changing the order of the two BricsCAD references, with no change in results. I also quit BricsCAD and Excel, and then re-loaded them, without change in results.

For easy reference, here is the part of the code I am refering to.

    'if autocad is open, obtain a reference to the autocad application
    'if autocad is not open, open autocad and obtain a reference to it
    'check that the application object is set
    'if there is an active drawing, obtain a reference to it
    'if there is no active drawing, create one and obtain a reference
    On Error Resume Next
    Set acadApp = GetObject(, "BricscadApp.AcadApplication")
    'if autocad not running then error - component cannot create object
    If acadApp Is Nothing Then
    Set acadApp = New AcadApplication
    acadApp.Visible = True
    End If

    If acadApp Is Nothing Then
        MsgBox "Autocad did not start"
        Exit Sub
    End If
    On Error GoTo 0

    On Error Resume Next
    Set acadDoc = acadApp.ActiveDocument
    If acadDoc Is Nothing Then
        Set acadDoc = acadApp.Documents.Add
        acadApp.Visible = True
    End If
    On Error GoTo 0

-Joe

terry priest · March 2017

under Debug pulldown is Compile VBAProject. you need to use that. it will find your code errors.

that's my code I posted. I got something similar from Mr. Samarras. It goes around. I changed his to exist in its own subroutine and use global variables. If you have changed it back to sit at the top of your subroutine, you might go to his site and compare what you have to his.

something wrong with the way your code is displaying. copying to the forum I noted that it was not formatting code properly in the message. Set Acadapp should not be green as if it were a comment. so that would explain why acadapp is nothing if that line is not running. on error previous to that should also be active. and comments above that should look like comments.

Joe Dunfee · March 2017

It was Samarras' code that was among the earlier failed efforts. It seems that this is an intermittent problem. But, I will try again with his code, since it is possible that I made an accidental edit to some of the code.

In regards to code formatting on the forum, when I first attempted it, the preview showed that it only put half of the code in the code box. So, I tried again, assuming that I did something wrong the 1st time. Then, it previewed with all the code in the code box. But, I did notice that the comments were not all shown correctly.

We had ongoing problems on the old forum with the code formatting. Perhaps it should also be abandoned on the new forum until the bugs are worked out.

-Joe

Joe Dunfee · March 2017

I just edited my code, to use a fresh copy of Sammaras' code, without success. The same errors come up as before.

Note that the only change to his code was to convert the statements that refer to AutoCAD,

            On Error Resume Next
            Set acadApp = GetObject(, "AutoCAD.Application")
            If acadApp Is Nothing Then
                Set acadApp = CreateObject("AutoCAD.Application")
                acadApp.Visible = True
            End If

to the BricsCAD version

    On Error Resume Next
    Set acadApp = GetObject(, "BricscadApp.AcadApplication")
    If acadApp Is Nothing Then
        Set acadApp = CreateObject("BricscadApp.AcadApplication")
        acadApp.Visible = True
    End If

So, it seems I am at the end of the road. BricsCAD does not seem to work reliably with VBA running from within Excel. BricsCAD customer support does not have Excel, so while they know VBA, they can't test the example they gave me.

PS, yes I tried to format the two code examples above as code. The 1st one would not work in the preview in a few attempts. However, it seemed to work after putting an extra line between the text above, and the code example. So, perhaps that is the unwritten rule.

-joe

Joe Dunfee · March 2017

I can't think of another way to do this project, so I continue to hack at it.

This is a routine From internet http://howtoautocad.com/excel-and-autocad-–-a-match-made-in-heaven-again/
to establish connection with BricsCAD. I modified it by changing "AutoCAD.Application" to "BricscadApp.AcadApplication"
I also changed the variable name to ones I was already using, acadApp . This version requires that BricsCAD already be running and have a document open.

Option Explicit

'Variables
Public acadApp As AcadApplication

Sub SendToCmdPrmpt()

    Set acadApp = GetObject(, "BricscadApp.AcadApplication") 'Get a running instance of the class AutoCAD.Application
    acadApp.ActiveDocument.Utility.Prompt "Hello from Excel!" 'Print a message to the AutoCAD® command line

End Sub

This gives me the error, Run-time error '429": ActiveX component can't reate object on the line

Set acadApp = GetObject(, "BricscadApp.AcadApplication")

Any more ideas to try?

[ The formatting of code is still not working properly. In this case, it is coloring comments green beyond the comment]
-Joe

terry priest · March 2017

BricsCad support does not have Excel? what do they have? I could not do engineering without excel.

attached is a screenshot of a file. I ran very large bricscad vba from excel, multiple modules and multiple forms. I only tried it for a month trial. It ran perfectly. everything I tried. you must also reference the bricscad libraries, I think there are two, under Tools, References. this must not show up "missing" and you should be able to compile. you should understand that variables declared in one sub are not available in another if they are Dim local. you should be able to step through your code with debug breakpoint and see exactly where the problem is.

it doesn't matter how your code formats in the forum but only how it looks in the module. start off with the smallest possible code and try to get that working.

I don't mean to tell you something you already know, but I am reviewing because I am sure it does work. it is of course possible that there is an installation issue. bricscad 2016 did not have vba in 64 bit. I only ran bricscad 2016 32 bit

good luck.

terry priest · March 2017

I don't have it installed right now. you would think somebody from bricsys could tell you the name of the library to reference in excel vba , and if those show up, that this should compile in the specific version you have.

dim acadApp As AcadApplication
Set acadApp = GetObject(, "BricscadApp.AcadApplication")

Joe Dunfee · March 2017

Thanks for the effort to help me Terry. But, I am confident that all the items you describe are done. E.g.. the references, etc.

I am working on BricsCAD v14 32 bit, because the 32 bit version supports VBA. Perhaps my problems only relate to v14. I will inquire about starting another trial with the most recent to see if that fixes anything.

I also have v13 installed, because it had some features that were taken away in v14. But, will also uninstall my v13, in case there is some sort of conflict with it that is interfering.

-Joe

terry priest · March 2017

it has to do with the referencing because you are not getting to first base (baseball reference). the trick of moving up the references in the list I found when I would switch back and forth home and work versions. you might try de-referencing, exit, come back, re-reference. if you have two versions, I would think you would have two sets of libraries, that is how autocad works.

Joe Dunfee · March 2017

I am confident that when I started the programming, the BricsCAD references were not referenced. I have also moved them up the list of references as far as they will move. None the less, I will remove them and then add them back.

I have also un-registered and re-registered it many times because I need to switch between my desktop system and my laptop. I am also now uninstalling BricsCAD v13. I uninstalled, but decided I should look in the registry.

But, there seem to be a zillion keys like the following;

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID{2E61E80A-8B5D-4894-84B0-2C2318FB6572}\InprocServer32
the data in the key is C:\Program Files (x86)\Bricsys\BricsCAD V13\axbricscaddb1.dll

I don't know what this does. I hope these are harmless, since there seem to be a zillion of them, each with a different sub-directory after the \classes\ category. I have deleted over 50 of them so far. But, have decided to not do the rest of them.

-Joe

Joe Dunfee · March 2017

I just finished doing all the uninstall that I can of my v13. I even hunted through the registry and deleted as much as I could of the v13 and v17 stuff.

I get the same error as before.

-Joe

terry priest · March 2017

there is early binding and late binding. I think samarras discusses that. if you late bind, you don't reference libraries and you dim in a generic way that is known to excel. that may have been going on.

Joe Dunfee · March 2017

I just looked over some information about Early vs. Late Binding. I see that Samarras mentions it, but I could not find where he used one method vs. another or explained it. But, I went to Microsoft's site, and read their explanation and examples.
https://support.microsoft.com/en-us/help/245115/using-early-binding-and-late-binding-in-automation

Then, I modified my code to use Late Binding, without success. Here is what I did.

'using the late binding approach
Option Explicit

'Variables
Public acadObj As Object

Sub SendCmdLateBind()

    Set acadObj = CreateObject("BricscadApp.AcadApplication") 'Get a running instance of the class AutoCAD.Application
    acadObj.ActiveDocument.Utility.Prompt "Hello from Excel!" 'Print a message to the AutoCAD® command line

End Sub

This generates the error; Run-time error '213##### [a bunch of numbers], Automation error. The requested operation requires elevation.
on the line ;

Set acadObj = CreateObject("BricscadApp.AcadApplication")

Do you think I am properly writing the code to do late binding?

-Joe

terry priest · March 2017

i don't know, it looks ok. here is a page that uses late binding, notice his dim statements are generic, dim acadapp as object, but he still has to connect as you did in the code above. i think you should claim its broke and open up a support ticket and make them send you a file that works. see if it works for you.

http://www.myengineeringworld.net/2014/10/send-autocad-commands-from-excel-vba.html

Joe Dunfee · March 2017

The example you just linked me to was adapted to work with BricsCAD a few weeks ago. it created most of the entities successfully. The problem with it, at the time, is that it always opened a new instance of BricsCAD. But, now it does not get past the part where it would normally create the new instance.

On a new thought, and to speculate rather blindly.. cold it be, that when I uninstalled other versions of BricsCAD, the copy of the .dll reference files VBA was seeing, were also taken away. One folder was actually a copy of the entire BricsCAD v14 directory, that I had made as a back up when trying to fix another, unrelated problem. I will try to manually browse to those files, and see if it re-educates Excel VBA about their locations.

-Joe

Joe Dunfee · March 2017

Failed again.

I also just now tried a more brute-force method of loading a reference.

Dim VBAEditor As VBIDE.VBE
Dim vbProj As VBIDE.VBProject

Set VBAEditor = Application.VBE
Set vbProj = ActiveWorkbook.VBProject

vbProj.References.AddFromFile "C:\Program Files (x86)\Bricsys\BricsCAD V14 en_US\axbricscadapp1.dll\3"
vbProj.References.AddFromFile "C:\Program Files (x86)\Bricsys\BricsCAD V14 en_US\axbricscaddb1.dll\3"

However this failed to even compile, because it wants the reference files to already be loaded before I compile the code.

I have no more leads to try to track down, and I have spent about a week and a half, total time trying to get this VBA program working from inside Excel. It has been problematic from the beginning, and at best, only partially worked. I qam now back on the idea that Excel VBA program on my system will not successfully work with BricsCAD v14 reliably, and even if it is theoretically able to work, and something is broken in just my computer, I have no way to troubleshoot or fix it with my own skills.

-Joe

Joe Dunfee · March 2017

Interesting action on the forum. It does automatic formatting.

I did not format the VBA code in the above message as code using the markdown method. Rather, I pasted the code which included some space in front of each line. The forum automatically formatted it as shown.

-Joe

Linh7cad · May 2017

I get in the same problem, then what I found that work is:
Set bCAD = CreateObject("BricsCADApp.AcadApplication.17.0")

Joe Dunfee · May 2017

I am sorry, but my command of VBA is not so great when it comes to using other apps. I think this has been confounded by my ongoing problems and seeming random scattering of ways to command other programs. Also, my mind has been out of this particular program for so long, I my recollection of the details is also poor.

When I attempt to insert your routine into my program (after changing the BricsCAD version #, it complains that bCAD varible has not been defined. So, I check and see that I have a differently named variable.

So, I change your line to use my variable name,, but it still does not start, and brings up my message box "BricsCAD did not start". Here is the code as it is with your line inserted and my original one rem-ed out.

`Option Explicit

'Declaring the Sleep subroutine so I can use it in the program.

If VBA7 And Win64 Then

'For 64 bit Excel.
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Else

'For 32 bit Excel.
Public Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

End If

'Variables
Public acadApp As AcadApplication
Public acadDoc As AcadDocument
Dim CountX As Integer
Dim CountY As Integer
Dim CurrentCol As Integer
Dim CurrentRow As Integer
Dim StartofScript As Integer
Dim EndofScript As Integer

Sub SendScript()
' Finds the 1st column that says "Start Script" and then sends each cell to
' BricsCAD until it finds "End Script".

'------------------
' Routine From BricsCAD forum to get the document ready
'------------------
'if autocad is open, obtain a reference to the autocad application
'if autocad is not open, open autocad and obtain a reference to it
'check that the application object is set
'if there is an active drawing, obtain a reference to it
'if there is no active drawing, create one and obtain a reference
On Error Resume Next

'Linh7cad's version of this line, with the variable name and version changed
Set acadApp = CreateObject("BricsCADApp.AcadApplication.14.0")
'XXXX Set acadApp = GetObject(, "BricscadApp.AcadApplication")

'if autocad not running then error - component cannot create object
If acadApp Is Nothing Then
Set acadApp = New AcadApplication
acadApp.Visible = True
End If

If acadApp Is Nothing Then
MsgBox "BricsCAD did not start"
Exit Sub
End If
On Error GoTo 0

On Error Resume Next
Set acadDoc = acadApp.ActiveDocument
If acadDoc Is Nothing Then
Set acadDoc = acadApp.Documents.Add
acadApp.Visible = True
End If
On Error GoTo 0

'proceed with the process
acadDoc.SendCommand (Chr(27) & Chr(27)) 'Send Esc keys to cancel any running command in BricsCAD

'cycle through the cells of the script

CurrentRow = ActiveCell.Row 'set current row

'find the 1st and last command by searching for "Start Script"
CountX = 1
Do
If Cells(CurrentRow, CountX) = "Start Script" Then
StartofScript = CountX + 1
ElseIf Cells(CurrentRow, CountX) = "End Script" Then
EndofScript = CountX - 1
CountX = 200
End If

CountX = CountX + 1

Loop While CountX < 200

'check if there is a valid start and end of script marker. If not end the program.
If StartofScript < 5 And EndofScript < 7 Then
MsgBox ("Searched for the text 'Start Script', which indicates the start of a scriput. But it is not found in this row. Program ended")
End
End If

'Loop through the script and send to BricsCAD
For CountX = StartofScript To EndofScript
If Cells(CurrentRow, CountX).Value <> "Skip" Then
If Cells(CurrentRow, CountX).Value = "Enter" Then
acadDoc.SendCommand (Chr(13)) 'just send an enter character
Else
acadDoc.SendCommand (Cells(CurrentRow, CountX).Value & Chr(13)) 'actually implements the command.
End If
End If

Sleep 50 'pause the program for a few milliseconds to make sure BricsCAD has time to process

'clean things up so it will run properly a 2nd time if called.
Set acadDoc = Nothing
Set acadApp = Nothing

End Sub`

Also, I have attached it with the Excel file it should work with.
-Joe

Joe Dunfee · May 2017

I should have mentioned that the version above has the portion where I modified it, in an attempt to use your idea. It is marked with your user name.

-Joe

Joe Dunfee · June 2017

I found the cause of the problem. Roy Klein Gebbinck had sent me an example in LibreOffice's Calc program. However, I got an error until I set LibreOffice Calc to "Run as Administrator". I did a similar change to Excel, and suddenly my VBA to talk to BricsCAD was now working.

My system administrator had some security settings, where some programs have to be set to "run as administrator" to be able to do certain things. It worked at first, perhaps because Windows had not initially noticed, or because Windows update had changed something.

I should mention that getting Excel to run as administrator took more than just changing the setting in the shortcut. In the shortcut, it is ghosted. So, you first have to track down the Excel.exe file, and change that to run as administrator. Then, you will be able to edit the shortcut and set it to run as administrator.

-Joe

VBA was working for a while, then error 91

Comments

If VBA7 And Win64 Then

Else

End If