[Solved] [Calc] Object Variable Not Set - Parameter error

[eddyparkinson]

Passing "nothing" from a cell formula to vb - gives an error.

How to catch the error? When I call "StrSpliter", from a cell, passing nothing as a parameter, I get "Object Variable Not Set".

The problem is the cell formula sometimes passes "nothing" and I get an error (or 10,000 errors, so I need to terminate office and restart)

Running "TestStrSplit" gives the same error.

Code: Select all

Sub TestStrSplit
   Dim a
   Dim s As String
   Dim i As Integer
   
   s = "hello OOoBasic programmers, this   is       another  solution"

   a = StrSpliter(Nothing," ")
   MsgBox(join(a,chr(13))
End Sub

Function StrSpliter(byval s As String, SplitChar As String)
  StrSpliter = "a"
End Function

[Zizi64]

Code: Select all

a = StrSpliter(Nothing," ")

Is the -Nothing- a variable, or a string constant in your code?

If it is a string constant, then you need use the quotes:

Code: Select all

a = StrSpliter("Nothing"," ")

If it is a variable, then you must declate the type, and must give a value for it.

Code: Select all

Dim Nothing as string
Nothing = "dfghsfhdfjhdfjudfudrtuzdtz"         'or:
Nothing = " "          'or:
Nothing = ""        
a = StrSpliter(Nothing," ")

[Zizi64]

Code: Select all

MsgBox(join(a,chr(13))

The input parameter of the Join function must be an Array. (See it in the Help)
The variable "a" is a pure string variable in your example, but not an Array.

[RPG]

Hallo

Nothing is a defined object in StarBASIC. It contains nothings as the word say. It is an empty object

Romke

[eddyparkinson]

Zizi64 - the problem is with blank spreadsheet cells.

If I put this in cell A1
=StrSpliter(A2," ")

it gives error "Object Variable Not Set" when A2 is blank.

how to stop this error popping up when a cell is blank?

[eddyparkinson]

Answer

Code: Select all

Function StrSpliter(s, SplitChar As String)
  StrSpliter = "a"
End Function

removing the type from the parameter declaration fixed the issue

Thanks to RPG for telling me which language ref to look this up in.