You cannot call a method on a null-valued expression - general

Question

You create a script, it works for some time, then out of the blue it starts crashing with "You cannot call a method on a null-valued expression" or "The property 'property name' cannot be found on this object. Verify that the property exists and can be set.". What does this mean?

Hoggish

Hmm someone decided to collapse all null reference based questions into one? Since this is a self-answer, might be this one if edited properly could be the rally popint instead – Hoggish

This question is being discussed on Meta. – Schluter

@mklement0 cc: meta.stackoverflow.com/q/424403 – Tympany

Could you provide us with an example? I'm quite sure not all scripts experience this problem. – Nickienicklaus

@Nickienicklaus say in my answer there is an example of Powershell invoking Excel to search for a value, then querying the value found without checking for null. In fact, checking for null is required in a TON of places in code, be it Powershell, C# or C++ (or actually most languages that have methods that can return null or non-null). – Hoggish

So, you should be checking for null in such instances? – Nickienicklaus

@Nickienicklaus yep; if a function may return null, there should be a check if it just did, and a branch in code designed to react on this happening. Many people, programmers or "clerks", get used to it not returning null and forgo writing error handling code, and soemtimes panic when something unusual happened and the result becomes null. – Hoggish

Yes, and then they'll (hopefully) learn to check for null values when necessary :) I fail to see the issue here. – Nickienicklaus

Answer 1

This is a Powershell version of "null pointer exception". This exception arises every time you attempt to query a variable that appears to be null. To determine what variable is null and where, you need to read the stack trace and the line/symbol numbers of the line in question. An example:

You cannot call a method on a null-valued expression.
At E:\temp\testsest.ps1:35 char:12
+         If($Search.value() -contains $SearchString)
 +            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Let's parse the error message. First, there is a wording that is in the title of this question. If you are about to ask a question with this wording, you will get a set of similar questions proposed by StackOverflow. But there is more in the error description. Second line shows script, line and character number of the first character of an expression that generates this exception. Here, a request is made to $Search.value() querying if it -contains $SearchString. The wavy underline separates the expression in full, although the proper way would be underlining only $Search.value(). Next, there is a CategoryInfo and FullyQualifiedErrorId, the latter saying "Invoke method on null", omitting "pointer" or "variable".

Now, let's debug the message. Here, the only method that's about to be called is value(), this means $Search is equal to null. Therefore, we need to get upwards from the line 35 of the script and find a place where a value is last assigned to the variable in question. This particular script had a query to Range.Find() which returns null if there's no match to the searched string. An excerpt:

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$ExcelWorkBook = $Excel.Workbooks.Open($ExcelPath)
$ExcelWorkSheet = $Excel.WorkSheets.item("$location")


$Range = $ExcelWorkSheet.Range("A1").EntireColumn
$Search = $Range.find($user) # <<< here we get null

If($Search.value() -contains $user) 

So, we have found where do we receive a null.

Remedies vary, but all include checks against $null. In this case it's enough to check $Search for null, and return "Nothing found" if it is indeed null. It might be not as simple, there might be more structures that can be null, like in $a.b.c.someMethod() - here either $a, $a.b or $a.b.c is null, so you need to check all of the outcomes. There are also situations where a complex structure is returned, and is expected to have a value in a certain field, but the field is not populated, therefore trying to use that field's value will produce an exception.

The moral is: If you receive an exception speaking about "null-valued", you have not expected something to return null, and you have to add checks for null (or, in fact, any unexpected) values before attempting to use the result.

Hoggish

Answer 2

go to "Programs and Features", choose Excel-Application, right-click "Change", "Quick Repair" solved my problem.

Only with "$excel.visible = $true" i got a better error-message (unable to cast com object of type 'microsoft.office.interop.excel.applicationclass', which helped me to find the solution on a microsoft-page https://learn.microsoft.com/en-us/answers/questions/258475/unable-to-cast-com-object-of-type-microsoft-office)

Bufordbug