CODEWITHSUNDEEP
excelvbauser-defined-functions
larryengineering
larryengineering

Reputation: 3

Excel VBA UDF Returns #VALUE Error When Adding Or Deleting Unrelated Sheet

First time posting, apologies if I make any mistakes!

So, I'm having a pretty strange problem with my UDF. In my workbook, I have an invisible 'template' sheet named "Standard Phase Sheet", and a subroutine that a user can activate which copies that template sheet into a new, visible sheet that the user can then work with. There will be many copies of that template sheet throughout the workbook, but they will all have unique names.

My UDF is on that template sheet in several spots, and thus on every copy of the template sheet that a user makes. When working within one of these sheets, the UDF works just fine, and returns the values I'd expect.

However, when a user ADDS a new copy of the template sheet, SOMETIMES the UDF goes haywire and returns #VALUE errors in every place the UDF is being used.

Also, when a user DELETES one of the copies of the template sheet, the UDF ALWAYS goes haywire and returns #VALUE errors in every place the UDF is being used.

I'm not using ActiveSheet or anything like that, and I believe I'm correctly giving full references to the ranges I'm working with within the UDF. Any help will be appreciated, I'm in a bind here! Code for the UDF is below.

Also, because I'm sure I'll be asked the question, the neColumn variable within my code is a public variable that I use in several subroutines and UDFs. It is defined at the beginning of my module. Also, I am using Option Explicit at the beginning of my module as well.

Thank you!

Public Function fSum(ByVal Target As Range, bExtended As Boolean) As Single

'This function returns a sum, based on a range provided in the cell that holds the function.
'It checks to see if that line item has been marked as Non-Extended, based on the NE column
'that can be check marked. If that line item is marked NE, then only the NE sum columns can
'use that line item as part of their sum, and those values are removed from the E columns.

Dim sSum As Single
Dim i As Integer
Dim n As Integer

'This small section is used to determine complete references to the cell calling the function.

Dim sheetName As String
sheetName = Application.Caller.Parent.Name

'Loop through provided range, and sum up the contents based on whether they have been marked NE or not.

i = 1
n = Target.row
sSum = 0

If Sheets(sheetName).Visible = True Then

While i < Target.Rows.Count

    If (bExtended = True) Then

        If Sheets(sheetName).Range(neColumn.Address).Cells(n, 1) = vbNullString Then

            sSum = sSum + Sheets(sheetName).Range(Target.Address).Cells(i, 1).Value

        End If

    Else

        If Sheets(sheetName).Range(neColumn.Address).Cells(n, 1) <> vbNullString Then

            sSum = sSum + Sheets(sheetName).Range(Target.Address).Cells(i, 1).Value

        End If

    End If

i = i + 1
n = n + 1

Wend

End If

fSum = sSum

End Function

Upvotes: 0

Views: 258

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

The reason is that your neColumn variable has become Nothing, because Excel is Volatile.

I assume that the start of your module looks something like this:

Option Explicit

Public neColumn As Range

Sub Auto_Open()
    Set neColumn = Sheet1.Range("A1:B2")
End Sub

When you open the Workbook, you call the Auto_Open Sub to Set the neColumn variable. However - when certain actions occur, Excel rebuilds the VBA, which resets the Public Variables (such as neColumn) to their defaults (which, for an Object such as a Range, is Nothing). An easy way to trigger this is by deliberately throwing an error, such as attempting to run this:

Sub ThrowErr()
    NotDefined = 1
End Sub

You can make it more visible to you by adding the following line to your fSum code:

If neColumn Is Nothing Then Stop

You either need a way to restore neColumn when it has been reset to Nothing, OR find a non-volatile way to store it.

I am assuming that this is not suitable to become a Const, because otherwise it already would be but you could turn it into a Named Range, or store the Address in a hidden worksheet / CustomDocumentProperty. These options would also allow you to store neColumn when the Workbook is saved for when you reopen it

Upvotes: 1

BigBen
BigBen

Reputation: 49998

Summarizing the comment thread in an answer for posterity:

  • I'm not sure why exactly you see this behavior.
  • There would be ways to better this UDF (including using Long instead of Integer, preferring a Do While...Loop to While...Wend, removing the .Visible check...
  • But in any case, it does feel like this is just replicating the functionality of SUMIFS so you might just consider going that route.

Upvotes: 1

Related Questions