Contact Customer Service
Please select a product group for appropriate contact information on the product you purchased or questions before you buy.

Multi Function Center®

7777 North Brother Blvd.
Bartlett, Tennessee 38133
1-877-BROTHER (877-276-8437)
901-379-1215 (fax)
9:00am to 8:00pm Eastern Time

Excel VBA question:

Anyone have any clue why the Year function wouldn't be working for me? If I use Year(somedateserialnumber or text here), I get a compile error with the message "Expected Array". If I try to use Application.WorksheetFunction.year, it compiles but bombs and I get the error message

Run-Time error '438': Object doesn't support this property of method.

The Month function works fine, but Year doesn't. WTF?

You haven't accidentally changed your computer to European date formatting, have you?

Also, does the Year function work outside of VBA (i.e. in a cell)?

Yes, it works in a cell.

I still don't know why it's not working in VBA, but i found a workaround using the DatePart function.

Now, to figure out why Application.WorksheetFunction.Sum isn't working either...

Hey, all. What happens if I take my old iPod (which was permissioned to my old computer, which has gone to the great network in the sky) and hook it up to the new computer/iTunes? Will it wipe the info on the old iPod and upload the new iTunes?)

Now, to figure out why Application.WorksheetFunction.Sum isn't working either...

Um... have you tried rebooting?

I'd also go into the VBA window and check Tools/References and make sure none are broken.

When we have weird problems like this in Access, sometimes a decompile/recompile will fix things. Dunno how to do a decompile in Excel (in Access you do it with a command-line switch) but you could try copying the code in question and pasting it into a new Excel spreadsheet VBA module.

Application.WorksheetFunction.Sum was working; I was adding up a range of cells before writing the values of that range to the worksheet. My bad. Still doesn't explain the Year function issue but I should be good.

Vortex, I believe the answer is yes (I'm looking at this now.) It looks like there are programs to allow you to pull the files from the iPod onto the computer.

huh. Jon, that's odd. My guess is that some library you have referenced is overriding the Year function somehow. VBA apparently lets you overwrite functions with impunity. At least, if I write the code below:

Sub test()
MsgBox Year(Now)
End Sub

Function Year(bob() As Date)
Year = 2008
End Function

in Excel 2004 on a Mac, it gives the error "Type Mismatch: Array or User-Defined Type Expected" when I try to run test.

Extra reading has shown me (and testing has confirmed) that IF that's the problem, you can solve it by using VBA.Year instead of Year.

That is, the code below correctly returns "2008" instead of an error:

Sub test()
  MsgBox VBA.Year(Now)
End Sub

Function Year(bob() As Date)
  Year = 1999
End Function