Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    VBA Function in Excel97

    Is it documented somewhere that a Function defined in an Excel module cannot have a side-effect (e.g. changing a cell value or property)?

    I was trying to show someone that this can happen by accident (not good programming practice), but when I do try to set a cell value inside a Function I get Error 1004.
    However, I haven't seen it explicitly documented that you can't do this.

    Jeremy

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function in Excel97

    Jeremy: If you are asking if it is possible for a function to make a modification to a sheet, other than by returning a value, the answer is no. You will get an error if the code tries, even if the modification is attempted from a Sub Procedure called from a function.

    If you are asking if it is written in the official documentation and where to find it, I am sure that it is but I don't know where you might find it.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: VBA Function in Excel97

    Thanks - I suspected it wasn't possible (certainly not a good idea), but when I looked at the Function help online, I couldn't see anything that explicitly prevented it!!

    Jeremy

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function in Excel97

    It definitely is explicitely prevented. I can't find where in the help file it might explicitely say that either. I also can't find it in any of the books I have here at my office.
    Legare Coleman

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA Function in Excel97

    Legare,
    I must be missing something here - I have written functions that change data on a worksheet e.g:
    Function ListTuesdays()
    Dim dblDateSerial As Double, lngDateAdd As Long, intRow As Integer
    dblDateSerial = CDbl(Date)
    intRow = 1
    For lngDateAdd = 1 To 730
    If Application.WorksheetFunction.Weekday(dblDateSeria l + lngDateAdd, 1) = 3 Then
    ActiveWorkbook.Sheets(1).Cells(intRow, 1).Formula = CDate(dblDateSerial + lngDateAdd)
    intRow = intRow + 1
    End If
    Next 'lngDateAdd
    ListTuesdays = True
    End Function
    Sub TryFunction()
    Dim var
    var = ListTuesdays
    End Sub
    it's basically just a sub but returns true if it succeeds. I find it sometimes helps me to see where things are failing. The sub at the end I added just to see if I could run it from a workbook and I can.
    Is this an O2k thing? (I can't remember if I did this when I had 97)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: VBA Function in Excel97

    Your example works by executing the TryFunction procedure. When you define a custom function, you can generally use that function in a spreadsheet cell. If you enter =ListTuesdays() in a cell, it will return #VALUE! rather than populating the column A with Tuesdays. I think that's what Legare is referring to(?)/.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA Function in Excel97

    Looks like I misread one of his posts - he said even if the function calls a sub not the other way round.
    Everyone please ignore me - I'm going to slink away quietly.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function in Excel97

    No, you didn't miss a post, just a little misunderstanding. The function has to be used as a worksheet function for it not to be able to modify the worksheet. If the function is used in a VBA Sub or Function that was not initiated as a result of a formula on a worksheet, then there is no problem. However, if a function is initiated as a worksheet function, it can't modify the worksheet, and it can't call a Sub that modifies the worksheet. I don't think I made that clear enough.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •