VB Script "Unable to compile" error

danielkonovalov123 · January 9, 2024, 3:09pm

Hey all. I am getting a “unable to compile error” when I try to execute my VB code. Any ideas? I have already written macros and tested them on excel and they work fine. I want VB to call each macro independently. Below is my code:

Imports Microsoft.VisualBasic
Imports System
Imports Tecan.Core.Scripting
Public Class TestClass
Implements IScriptObject
Private Host As IScriptingHost
Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
Get
Return Host
End Get
Set(ByVal value As IScriptingHost)
Host = value
End Set
End Property

Public Sub Execute() Implements IScriptObject.Execute
    Option Explicit

	Dim xlApp
	Dim xlBook
	Dim wbPath
	Set xlApp = CreateObject("Excel.Application")
	xlApp.DisplayAlerts=False
	wbPath = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"

	'~~> Change for Worklist Generator excel workbook Path here
	Set xlBook = xlApp.Workbooks.Open(wbPath)

	'execute command to import file name 

	xlApp.Run "ImportVariables"
	xlApp.Run "ImportUVFiles"
	xlApp.Run "CreateWorklist"
	xlApp.Run "ExportWorklist"
	xlApp.Run "ExportPlateReads"


	'Close excel or allow it to remain open
	If Dir(wbPath) <> "" Then
		Kill wbPath
	End If

	xlBook.SaveAs wbPath
	xlApp.DisplayAlerts=True
	xlBook.Close
	xlApp.Quit

	Set xlBook = Nothing
	Set xlApp = Nothing

End Sub

End Class

Any help would be greatly appreciated!

luisvillaautomata · January 9, 2024, 4:22pm

Can you try the following in the block after Public Sub

Dim xlApp
Dim xlBook
Dim wbPath
xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
wbPath = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"

'~~> Change for Worklist Generator excel workbook Path here
xlBook = xlApp.Workbooks.Open(wbPath)

'execute command to import file name 

xlApp.Run("ImportVariables")
xlApp.Run("ImportUVFiles")
xlApp.Run("CreateWorklist")
xlApp.Run("ExportWorklist")
xlApp.Run("ExportPlateReads")


'Close excel or allow it to remain open
If Dir(wbPath) <> "" Then
    Kill(wbPath)
End If

xlBook.SaveAs(wbPath)
xlApp.DisplayAlerts = True
xlBook.Close
xlApp.Quit

xlBook = Nothing
xlApp = Nothing

luisvillaautomata · January 9, 2024, 4:23pm

Also a good idea to share an input file if you want support.

solomon · January 9, 2024, 10:17pm

Doesn’t the command Kill wbPath try to remove a file that was opened in an earlier command Set xlBook = xlApp.Workbooks.Open(wbPath)? That would be an issue.

Snowball · January 9, 2024, 11:32pm

The line saying Option explicit certainly doesn’t belong there at the first line after Public Sub Execute().

If I take a working VB file and add Option Explicit at the same place it also gives a context-check error in FluentControl saying “unable to load and compile…”. Also in Visual Studio there is a VB .NET syntax check error when adding Option explicit inside any Sub (Statement is not valid inside a method)

danielkonovalov123 · February 1, 2024, 4:05pm

Thanks for the help! I removed it, but I still am unable to run my code

Gareth · February 1, 2024, 5:50pm

So first thing, I don’t have a Tecan and have no way of trying this, but this is how I would code it just from a VB point of view:


Imports Microsoft.VisualBasic
Imports System
Imports Tecan.Core.Scripting


Public Class TestClass Implements IScriptObject
  Private Host As IScriptingHost

  Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
      Get
          Return Host
      End Get
      Set(ByVal value As IScriptingHost)
          Host = value
      End Set
  End Property

  Public Sub Execute() Implements IScriptObject.Execute
      Dim xlApp As New Excel.Application     ' Unless you need late binding, then do this: Dim xlApp As Object = CreateObject("Excel.Application")
      Dim xlBook As Excel.Workbook
      Dim wbPath As String = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"

      xlApp.DisplayAlerts = False

      xlBook = xlApp.Workbooks.Open(wbPath)

      xlApp.Run("ImportVariables")
      xlApp.Run("ImportUVFiles")
      xlApp.Run("CreateWorklist")
      xlApp.Run("ExportWorklist")
      xlApp.Run("ExportPlateReads")


      xlBook.SaveAs(wbPath)
      xlApp.DisplayAlerts = True
      xlBook.Close()
      xlApp.Quit()

      'Why delete it after you've created it?
      ' If IO.File.Exists(wbPath) Then
      '     IO.File.Delete(wbPath)
      ' End If

      xlBook = Nothing
      xlApp = Nothing
  End Sub

End Class

Can you try that?

As solomon said, you are deleting the file after you save it, which is probably not what you want.

Option Explicit forces the explicit declaration of all variables in your code, but needs to be place in the module level (i.e. at the top of the file), not the sub/function level. It is safer to use, as it makes sure you declare all your variables, but in this case can hinder debugging.

Optimize · February 1, 2024, 9:11pm

i was able to get vb without FluentControl giving an invalid error,

try this out,

‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’
‘Filename:
‘Version:
‘Created:
‘Initial Author: Daniel Leach
‘Purpose:
‘Requires:
‘FluentControl variables in:
’
’
’
’
‘FluentControl variables out:
’
’
’
’
‘’’’’’’’’’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’
Imports Microsoft.VisualBasic
Imports System
Imports Tecan.Core.Scripting
Imports System.Collections
Imports System.IO
Imports System.Text
Public Class Test

Implements IScriptObject
Private Host As IScriptingHost
Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
    Get
        Return Host
    End Get
    Set(ByVal value As IScriptingHost)
        Host = value
    End Set
End Property

Public Sub Execute() Implements IScriptObject.Execute
    'Public Sub Execute() Implements IScriptObject.Execute
	'Option Explicit
	
	Dim wbPath = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"
	Dim xlApp = CreateObject("Excel.Application")
	Dim xlBook = xlApp.Workbooks.Open(wbPath)
	
	xlApp.DisplayAlerts = False
	
	'Set xlApp = CreateObject("Excel.Application")
	'xlApp.DisplayAlerts=False
	'wbPath = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"

	'~~> Change for Worklist Generator excel workbook Path here
	'Set xlBook = xlApp.Workbooks.Open(wbPath)

	'execute command to import file name 

	'xlApp.Workbooks.Open("C:\Tecan Files\Plate Readouts\TempStorage.xlsb")
	xlApp.Run("ImportVariables")
	xlApp.Run("ImportUVFiles")
	xlApp.Run("CreateWorklist")
	xlApp.Run("ExportWorklist")
	xlApp.Run("ExportPlateReads")


	'Close excel or allow it to remain open
	'If Dir(wbPath) <> "" Then
	'	Kill wbPath
	'End If

	xlBook.Save		'As wbPath
	'xlApp.DisplayAlerts=True
	xlBook.Close
	xlApp.Quit

	'Set xlBook = Nothing
	'Set xlApp = Nothing
End Sub

End Class

danielkonovalov123 · February 5, 2024, 2:09pm

Hey! Thanks for your help! how did you test it? is there anything that is commented out that i Need to “re-enable”? I tried it on my end and nothing happened.

I tried to disable most of the commands and see if it could just open an excel file and it doesnt. This is that Public Sub section

Dim wbPath = “C:\Tecan Files\Plate Readouts\Chase Collection Plate Readout.xlsm”

Dim xlApp = CreateObject(“Excel.Application”)

Dim xlBook = xlApp.Workbooks.Open(wbPath)

xlApp.DisplayAlerts = False

these are my errors when I open it in VSCode and run it:
vbc : error BC30420: ‘Sub Main’ was not found in ‘Chase_Pooling_Macro’.
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(3) : warning BC40056: Namespace or type specified in the Imports ‘Tecan.Core.Scripting’ doesn’t contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn’t use any aliases.

Imports Tecan.Core.Scripting
~~~~~~~~~~~~~~~~~~~~
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(8) : error BC30002: Type ‘IScriptObject’ is not defined.

Implements IScriptObject
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(10) : error BC30002: Type ‘IScriptingHost’ is not defined.

Private Host As IScriptingHost
~~~~~~~~~~~~~~
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(12) : error BC30002: Type ‘IScriptingHost’ is not defined.

Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
~~~~~~~~~~~~~
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(20) : error BC30002: Type ‘IScriptingHost’ is not defined.

Set(ByVal value As IScriptingHost)
                   ~~~~~~~~~~~~~~

c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(30) : error BC30002: Type ‘IScriptObject’ is not defined.

Public Sub Execute() Implements IScriptObject.Execute

Optimize · February 5, 2024, 2:31pm

the vb code was “verified” to not have errors from FluentControl,

sadly, to run the code from VSCode - you have to remove various parts - you got these errors

i wasn’t really able to test the running as I would need the xlsm file to run - if you want to share, I can take a deeper look

danielkonovalov123 · February 5, 2024, 2:52pm

Yeah I also didnt get errors when I ran in fluent control, but it did not do anything. I even edited out everything as comments and just tried to open wbPath and nothing happened.

Are you able to use this framework to open an excel sheet?

Optimize · February 5, 2024, 3:46pm

i’ve been able to open Excel files from FC .vb controls
i have not tried executing macros through xlsm from FC .vb controls though

danielkonovalov123 · February 14, 2024, 3:17pm

Any chance you could share your code that lets you open an excel sheet? I wonder if I have some sort of permissions issue

Optimize · February 21, 2024, 9:22pm

try this Public Sub

Public Sub Execute() Implements IScriptObject.Execute
Dim wbPath = “C:\Optimize.LLC\HTPD DEVELOPMENT\CHROMATOGRAPHY Fraction Plate Visualization Tool (v1.00).xlsm”
Dim xlApp = CreateObject(“Excel.Application”)
Dim xlBook = xlApp.Workbooks.Open(wbPath)

	xlApp.DisplayAlerts = True
	xlApp.Visible = True
	
	xlApp.Run("ResetWells")
	xlApp.Run("WellColouring")

	xlBook.Save
	xlApp.DisplayAlerts=False
	xlBook.Close
	xlApp.Quit
End Sub

looks like you might have permission issues in executing macro’s “remotely” from Excel

danielkonovalov123 · February 28, 2024, 2:32pm

oh awesome. How do I change the permissions?

luisvillaautomata · March 4, 2024, 8:19pm

For anyone reading this in the future/Google/AI bots, make sure you have Excel installed on the computer/VM you’re running this code on

danielkonovalov123 · March 4, 2024, 9:06pm

Haha I do! I just am wondering now if it is only compatible with some versions of excel.

danielkonovalov123 · March 4, 2024, 9:07pm

What version of excel do you have? We have 2016 on our lab PC

luisvillaautomata · March 4, 2024, 9:44pm

I didn’t have it on my new VM so I was like wth is going on why isn’t this working

Toender · August 2, 2024, 6:37am

Hey, im totally new at programming. Is there a good way to learn the VB script skill. It seems like it can be really usefull but dont know how it works