I have created an Add-In for Excel which determines the name of ActiveSheet
and ActiveWorkbook
. The code I used is below. When I run the Add-In it is showing the above mentioned error after the message box "variables set". But when I run it in macros it is working fine. I don't understand what is happening with the Add-In. Could anyone help me with this?
Sub sheetvalues()
Dim bk As Workbook, sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Dim book As String, sht As String, i As Integer, j As Integer
Dim att(1 To 4) As String, att_col(1 To 4) As Integer
MsgBox ("variables set")
book = ActiveWorkbook.Name
sht = ActiveSheet.Name
MsgBox ("names set")
Set bk = Workbooks.Add
With bk
.Title = "MissingValues"
.SaveAs Filename:="MissingValues.xls"
End With
Set sht1 = bk.Sheets.Add
sht1.Name = "EndOne"
Set sht2 = bk.Sheets.Add
sht2.Name = "EndTwo"
Set sht3 = bk.Sheets.Add
sht3.Name = "EndThree"
MsgBox (book & " " & sht)
MsgBox ("completed")
End Sub
A common issue that causes this issue is forgetting to use 'Set' with assigning a value to a variable.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With