VBA - Range.Copy Method Error on Table with User-Defined Formula

Question

I've come across a fairly weird issue and I'm wondering if I'm doing something wrong or if this is an Excel bug (using Excel 2010 for PC, BTW)... To explain, let me create a simple scenario:

I define a very simple UDF that uses properties of a Range object, for example:

Public Function SillyThing(rng As Range) As Variant
    SillyThing = rng.IndentLevel
End Function

I have a simple table defined in my workbook (Table1, say) with only one row at the start:
```
Col1:      Col2:
<blank>    =SillyThing([@col1])
```
I have a column of cells in, say, Range("F2:F8")

I have the following Sub defined:

Sub Macro3()
    Range("F2:F8").Copy Range("Table1[col1]")
End Sub

Now, when I run that macro, it gives me #VALUE! error for the entire second column in my table (where the formula would get auto-filled).

However, if I use the following macro instead:

Sub Macro2()
    Range("F2:F8").Select
    Selection.Copy
    Range("Table1[col1]").Select
    ActiveSheet.Paste
End Sub

Then it works just fine!

Upon debugging, if I place a breakpoint in the UDF, I find that the Range object passed through in Macro3 has the following statement for many of its properties - : IndentLevel : <Unable to get the IndentLevel property of the Range class> : Variant : Module1.SillyThing

Obviously, I know how to fix it using the same idea as Macro2, but I'm really curious if this is a known issue or if there's something I'm missing / other workarounds I'm unfamiliar with.

Hope this makes sense!!

Thanks!!!

David Zemens · Accepted Answer · 2014-07-19 16:48:04Z

1

I notice the same thing as you do.

but I'm really curious if this is a known issue or if there's something I'm missing / other workarounds I'm unfamiliar with.

Not sure if it is a known issue, it certainly seems counter-intuitive. Why would it not work??? If I had to venture a guess, it's a bug. There is a known limitation of UDF's when called from the worksheet, that they cannot access some properties and cannot interact with other ranges that were not passed as an argument to the function itself, etc. But in this case the only range being accessed is the one passed as an argument to the function itself, so that doesn't even really make sense, but it's the first thing I think of.

Even though that explanation doesn't make sense... if I do this, the function errors at the ret = ... line:

Public Function SillyThing(rng As Range) As Variant
    Dim ret As Variant
    ret = rng.IndentLevel
    SillyThing = ret
End Function

I even tried this evaluating the range address indentlevel like [A2].indentLevel which raised the same error. The range does exist, and it's not a multi-cell range (which might be expected to raise an error).

However, if I do this instead, the function doesn't error and the table updates with the copy/paste:

Public Function SillyThing(rng As Range) As Variant

    SillyThing = rng & " 1"
End Function

So it certainly seems related to some inadvertent limitation that is preventing you from accessing the .IndentLevel property of the range argument.

But here is another slightly more elegant workaround: Instead of Macro2, try this -- at least better than using Select method :)

Range("Table1[Col2]").Copy  'Or Range("F2:F8").Copy - both seem to work the same
Range("Table1[Col1]").PasteSpecial xlPasteFormulas

And this method should also work:

Application.Calculation = xlCalculationManual
Range("E2:E5").Copy Range("Table1[Code]")
Application.Calculation = xlCalculationAutomatic
Application.Calculate

edited Jul 19, 2014 at 16:48

answered Jul 19, 2014 at 16:22

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Bustos Over a year ago

Thanks, David, that is EXACTLY the workaround I had ended up using - Like I stated in the question, it wasn't that I didn't know how to fix it, rather that it was a weird bug / quirk... It does make me happy to hear that it wasn't just something I was doing stupidly wrong... I'm going to leave the question open for a few more days hoping some else has knowledge of this, otherwise, I'll makr yours as the solution... Thanks for taking the time to check out the question!

Collectives™ on Stack Overflow

VBA - Range.Copy Method Error on Table with User-Defined Formula

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related