-1

I keep receiving an error on line 2 char 9 with the error

Expected end of statement.

I am new to working with VBScript but I have looked up several examples and I can not find where I am having an error on that line. Can you guys assist?

Edited for more info: If I remove the Dim row As Integer line running the script give me a type mismatch error. If I remove row = 150 and replace row further down with 150 the script updates the excel sheet as expected. 

Dim args, objExcel
Dim row As Integer 'I am having the error here. Note this comment is not normally here.

row = 150
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open "C:\Users\gria\Desktop\Test\GrossRevenueCopy.xlsm"
objExcel.Visible = False

objExcel.Run "InsertRow", row

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.close(0)
objExcel.Quit
user692942
  • 16,398
  • 7
  • 76
  • 175
Ardel
  • 165
  • 3
  • 14
  • What application / programming language is this code being written in? – YowE3K May 24 '17 at 21:22
  • I first did it in Notepad++ and then thought that may be the issue so I also tried it in the windows Notepad but same error no change. – Ardel May 24 '17 at 21:23
  • I tried to reproduce this in Excel 2013 - It complained that the instructions were not in a procedure, so I put everything from `row=150` and below into a `Public Sub foo()` . It worked fine for me. Need some clarification to better help you. – Kevin K May 24 '17 at 21:24
  • Note that this comment uses C# syntax; VBA comments are denoted by either a single-quote, or a `Rem` instruction. – Mathieu Guindon May 24 '17 at 21:24
  • But after having written it in Notepad, what application / programming language / development environment are you running it in? – YowE3K May 24 '17 at 21:24
  • How about using the VBE instead of Notepad? – Mathieu Guindon May 24 '17 at 21:24
  • @Mat'sMug that was to just throw out a comment. I am new to VBA so i just used java style comments. Those comments are not in the script when I try to run it – Ardel May 24 '17 at 21:25
  • Is this an entire module's code? Because most of that code is only legal inside a procedure scope. – Mathieu Guindon May 24 '17 at 21:26
  • @YowE3K I save it as a .VBA file then I activate it by double clicking the file. End game is to pass a row number from a java application to this script to update this excel file. – Ardel May 24 '17 at 21:26
  • @Mat'sMug Yes this is the entire code. – Ardel May 24 '17 at 21:27
  • 3
    OK, so you are trying to run it as VBScript. Get rid of the `As Integer` then. – YowE3K May 24 '17 at 21:27
  • Then it's not VBA, it's VBScript. VBA requires executable statements to be inside a procedure scope - @YowE3K, it's all yours =) – Mathieu Guindon May 24 '17 at 21:27
  • @Mat'sMug Your right VBScript. My bad T_T – Ardel May 24 '17 at 21:30
  • @YowE3K I edited the original question where I tried to do that. I get a type exception error. – Ardel May 24 '17 at 21:31
  • 1
    @Mat'sMug As soon as I see things like `Set objExcel = CreateObject("Excel.Application")` my first thought is "This is not Excel VBA (even if it is using the Excel Object Model)". Usually it ends up being Word VBA, or MSAccess VBA, but the complaint about the word `As` caused red flags to be raised. – YowE3K May 24 '17 at 21:34
  • 1
    @YowE3K agreed. I had to add my grain of salt [here](https://stackoverflow.com/a/44168928/1188513). – Mathieu Guindon May 24 '17 at 21:42
  • @Mat'sMug Thanks for the help and your link. Helped me understand these differences, and the original error, much clearer. – Ardel May 24 '17 at 21:57

1 Answers1

4

VBScript does not have the concept of a variable "type", so it complains if you try to define something As Integer.

So the simple solution is to replace

Dim row As Integer

with

Dim row
YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • When I edit the code to match I get the error Type mismatch on line 10, code: 800A000D – Ardel May 24 '17 at 21:33
  • 2
    @Ardel Try converting the parameter to an integer using `CInt(row)`. Note: I don't use VBScript, so I am guessing as to whether it includes a `CInt` function. Or change your `InsertRow` function to accept a `Variant` or an `Object` (depending on what type VBScript actually uses for its variables). – YowE3K May 24 '17 at 21:37
  • Would be `Variant`, given `row` is definitely not an `Object` reference. – Mathieu Guindon May 24 '17 at 21:50
  • @YowE3K chaing = row to = CInt(row) fixes all the issues and does exactly what I need. Thank you for all your help and patience – Ardel May 24 '17 at 21:56
  • @Mat'sMug I wasn't sure whether VBScript might be like VB.Net where everything is an object, even basic data types. (So in VB.Net `Dim x As Integer = 5: MsgBox x.ToString` is valid because `x` is an object which has a `ToString` method.) – YowE3K May 24 '17 at 22:12