none
Option Explicit Fails???? RRS feed

  • Question

  • Hi,

    I have developed a User Defined Function in VBA in Excel Office 365 ProPlus Version 1711.

    The problem is that the UDF works on my machine, but not other machines! Or you could say that Option Explicit fails on my machine, but not others.

    I have Option Explicit turned on. I forgot the declare a variable (actually renamed and missed updating an instance), but everything runs on my machine anyway, but it fails on user's computers! So, I know why it fails and it fails in an appropriate and expected fashion, what I don't know if why it works on my machine and that really worries me.

    I can easily fix this particular issue by carefully checking my variable declarations, but I would like to at least know what is happening to avoid this in the future.

    Relevant code below.

    Any ideas?

    Thanks!

    Ethan

    Option Explicit
    'A bunch of globally defined constants. No globally defined variables
    
    'A variety of Functions and Procedures
    
    'The problem function. The undeclare variable is "shiftForthispeak" (which got renamed shiftForThisTumorPeak, but not updated everywhere)
     Private Function FindLargestPentaShift(tumorPeaks As Variant, normalPeaks As Variant) As Double
          Dim normalPeak As Variant
          Dim tumorPeak As Variant
          Dim largestShift As Double
          Dim tumorPeakIsNormal As Boolean
          Dim difference As Double
          Dim closestLargerNormalPeak As Double
          Dim closestSmallerNormalPeak As Double
          Dim shiftForThisTumorPeak As Double
    
          ' Need to find the closest larger/smaller. Not the absolute largest shift from any peak!
          largestShift = 0
          For Each tumorPeak In tumorPeaks
              tumorPeakIsNormal = False
              shiftForThisTumorPeak = 0
              closestLargerNormalPeak = tumorPeak
              closestSmallerNormalPeak = tumorPeak
              For Each normalPeak In normalPeaks
                difference = tumorPeak - normalPeak
                tumorPeakIsNormal = tumorPeakIsNormal Or Abs(difference) < PeakBinWidth
                If tumorPeakIsNormal Then
                    shiftForthispeak = 0
                    Exit For
                Else
                    If difference > 0 And (tumorPeak = closestLargerNormalPeak Or Abs(difference) < Abs(tumorPeak - closestLargerNormalPeak)) Then
                            closestLargerNormalPeak = normalPeak
                        End If
                    If difference < 0 And (tumorPeak = closestLargerNormalPeak Or Abs(difference) < Abs(tumorPeak - closestSmallerNormalPeak)) Then
                            closestSmallerNormalPeak = normalPeak
                    End If
                End If
              Next
              If Not tumorPeakIsNormal Then
                If Abs(tumorPeak - closestSmallerNormalPeak) > Abs(tumorPeak - closestLargerNormalPeak) Then
                  shiftForthispeak = tumorPeak - closestSmallerNormalPeak
                Else
                  shiftForthispeak = tumorPeak - closestLargerNormalPeak
                End If
              End If
                If Abs(largestShift) < Abs(shiftForthispeak) Then
                  largestShift = shiftForthispeak
                End If
              Next
          FindLargestPentaShift = largestShift
    End Function.
    


    Ethan Strauss

    Wednesday, June 20, 2018 4:55 PM

All replies

  • Hello Ethan Strauss,

    Will it be related to specific workbook? If you copy the code to a new workbook, will you get the same issue?

    Have you tried to repair your office? or If you update Office to latest version, will you get the issue?

    Besides, it the code actually the UDF you called? As far as I know, we could not call an UDF which declared with "private". Did I misunderstand anything?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 21, 2018 2:17 AM
  • Hi.

     It seems to be specific to that workbook and saved copies of that workbook.

    When I copy the code to a new book, the unexpected behavior does not occur.

    I inserted a function (which was never called) before any other functions and the unexpected behavior stopped.

    I have not tried repairing office. This is a brand new Windows 10 machine which I have been using about a week. It is running Microsoft Office 365 ProPlus v 16.0.8730.2175.

    The code I have about is called by the actual UDF. That Function is not private.

    Thanks,

    Ethan


    Ethan Strauss

    Thursday, June 21, 2018 4:31 PM
  • Hello Ethan Strauss,

    Since your issue is only related to specific workbook, I would suggest you share the workbook so we could try to use it to reproduce your issue. Please remember removing any sensitive information first.

    For sharing a document, you could share it via Cloud Storage, such as One Drive, and then put the link address here.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 22, 2018 6:00 AM