Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Volatile function problems (Excel2003)

    I have a VBA function in Excel that takes a cell-value as input and is used to populate another cell with the outcome.

    Example:

    Cell A1 = "Test"

    Cell A2 = MyFunction(A1)

    Now cell A2 e.g. gives "tseT".

    I want the contents of A2 to update when I change A1, so I made the function "Volatile" (Application.Volatile). However another piece of VBA code I have, switches between this sheet and another one. Problem now is that as soon as I am on the other sheet, the function still fires, and AS THERE "A1" is empty, it fills cell A2 of my original sheet with nothing.....

    I tried to switch-off automatic calculation in my VBA, AND add a first line in my Function saying "if activesheet.name<>"Main Sheet" then Exit function" but both do not help.

    How do I solve this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Volatile function problems (Excel2003)

    I don't think you need the function to be volatile - a function will always be recalculated when the input cells change. You need volatile when the result should be recalculated when other cells than the function's input cells change.

    What does your function do? (I assume the string reversion was just an example)

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Volatile function problems (Excel2003)

    Hans,

    I made some more improvements and followed your advice to disable the volatile option. Seems to work now, thanks.

    The function is below, it's a Dutch thing mostly I believe. I have names in a list, example could be a woman who is called (I'm making this name up) "Anja de Graaf" with a maiden-name "van de Berg". In the Netherlands (again: as you know); we'll send mail as "Anja de Graaf - van de Berg".

    In my sheet where I keep the names I have two cells, one with the first name (no problem): "Anja". The other one however is written as "Graaf de - van den Berg" to allow sorting on last name. The function takes this cell and builds the mail-name from that. As there are several permutations (like we could have 0, 1 or 2 words 'in between' in both names), I couldn't come up with a simple Excel formula so I made a small VBA function.


    <pre>Function Naam_Maak(ANm As Range) As String
    Dim i1 As Integer, i2 As Integer, Naam As String
    'Naam
    Naam = Range("A1").Cells(ANm.Row, 1).Text
    i1 = InStr(Naam, " - ") 'Meisjesnaam
    If i1 <> 0 Then
    i2 = InStr(Naam, " ")
    If i2 < i1 Then 'Naam man met spatie
    Naam = Trim(Mid(Naam, i2, i1 - i2) & " " & Left(Naam, i2 - 1) & Mid(Naam, i1))
    End If
    Else
    i2 = InStr(Naam, " ")
    If i2 <> 0 Then 'Naam man met spatie
    Naam = Trim(Mid(Naam, i2) & " " & Left(Naam, i2))
    End If
    End If
    '
    Naam_Maak = Naam
    End Function
    </pre>


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Volatile function problems (Excel2003)

    You don't need volatile for this function, indeed, since the result only depends on the input range, not on other cells.

    I'd use separate columns for
    - First name (Anja)
    - Last name (Graaf)
    - Prefix of last name (de)
    - Maiden name (Berg)
    - Prefix of maiden name (van de)

Posting Permissions

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