Writing a value to an Excel cell

Question

I'm aiming to create a simple ribbon with a button that set cell (A1) value to "Hello World!" in the active work sheet.

I have this code but it returns an error and I have no idea what I'm doing wrong.

Screenshot of error:

Code:

Imports Microsoft.Office.Tools.Ribbon
Imports Microsoft.Office.Interop.Excel

Public Class Ribbon1

    Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet

        xlBook = xlApp.ActiveWorkbook
        xlSheet = xlBook.ActiveSheet

        With xlSheet
            .Range("A1").Value = "Hello World!"
        End With
    End Sub

End Class

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Bugs, Apr 27 '17 at 15:25

score 2 · Accepted Answer · answered Apr 27 '17 at 14:50

You need to add a new Workbook before you can do anything with it:

xlApp.Workbooks.Add()

I would also consider declaring a Range to use:

Dim xlRange As Excel.Range = xlSheet.Range("A1")
xlRange.Value = "Hello World!"

Your code would look something similar to this:

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add()
Dim xlSheet As Excel.Worksheet = CType(xlBook.ActiveSheet, Excel.Worksheet)

Dim xlRange As Excel.Range = xlSheet.Range("A1")
xlRange.Value = "Hello World!"

Also please turn Option Strict On:

Restricts implicit data type conversions to only widening conversions, disallows late binding, and disallows implicit typing that results in an Object type.

Writing a value to an Excel cell

1 Answers1