Consulting

Results 1 to 11 of 11

Thread: Excel Cells and VBA MAX Functions Stopped Working

  1. #1

    Excel Cells and VBA MAX Functions Stopped Working

    I use and Excel workbook as the data source for a Visio application. The workbook has three visible worksheets and four hidden ones. The worksheets hold technical specifications and other data for server and other shapes on Visio drawings. Rows in worksheets are linked to shapes. I use automation to refresh the server shape data.

    About a month ago, the Excel MAX function stopped working. I had been using the same code with the MAX function for over two years to return the max number in a column. All of a sudden, it would ignore all other rows in the column and return a zero.

    For over two years, I used VBA to populate a cells based on a drop down menu selected in another cell. For instance, when the OS is selected, the default data disk type, size, and other specifications are placed in separate cells. All of a sudden, the target cells were no longer being updated. Oddly enough, if I set a break point and step through the code, the drop down populates its target cell as it always had. If I run it normal, it does not.

    This makes absolutely no sense to me. Any help or ideas would be appreciated.

  2. #2
    This is the code I use to trigger auto-generation and validation routines:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
        '// Cloud Only Routines
        Call setResourceGroupName(Target)
        Call setFinancialCodeValue(Target)
        Call setVhdImageName(Target)
        Call setDataDisk1Defaults(Target)
        Call setDataDiskDefaults(Target)
        Call setCloudIP(Target)
        
        '// Cloud and On-Prem Routines
        Call setDeviceID(Target, C_SHEETNAME_CLOUD)
        Call setServerName(Target, C_SHEETNAME_CLOUD)
        Call setServerSize(Target, C_SHEETNAME_CLOUD)
        
        Call validateServerName(Target, C_SHEETNAME_CLOUD)
        Call validateDeviceNSG(Target, C_SHEETNAME_CLOUD)
        Call validateAppSectyGrp(Target)
        Call validateAvailSetName(Target)
        Call validateDataDiskSet(Target)
        Call validateStorageAccount(Target)
        Call validateResourceGrp(Target)
        Call validateCloudIP(Target)
        
        '// Set applicable column values to lowercase
        Call setTextToLowerCase(Target, C_SHEETNAME_CLOUD)
        
    End Sub
    This is the code I use to set the server name. This code has been working forever , until this past week.

    Public Sub setServerName( _
                ByVal Target As Range, _
                strSheetName As String _
                )
    
    
    'Name:   <site code><location><function code><unique identifier>-<sub-site identifier>
    '# char       4          2           3              3                     1
    On Error GoTo CleanExit
        
        If Target.Count > 1 Then GoTo CleanExit
        If Target.row = 1 Then GoTo CleanExit
        
        Dim lgTgtCol As Long
        lgTgtCol = convertLetterToLong(C_COL_LTR_SERVER_TYPE)
        
        If Not Target.Column = lgTgtCol Then GoTo CleanExit
    
    
        If strSheetName = C_SHEETNAME_ONPREM And Cells(Target.row, lgTgtCol) = C_LEGACY_SVR_ONPREM Then GoTo CleanExit
        
        Excel.Application.enableEvents = False
        
        If strSheetName = C_SHEETNAME_CLOUD And Target = C_LEGACY_SVR_ONPREM Then
        
            Dim strMsg As String
            strMsg = "This Server Type can only be used on the On-Prem worksheet."
                        
            Dim r As Integer
            r = MsgBox(Prompt:=strMsg, Buttons:=vbOKCancel + vbCritical + vbMsgBoxSetForeground, Title:="Error Manager")
            
            Cells(Target.row, Target.Column) = ""
            
            GoTo CleanExit
        
        End If
        
        Dim lgDestCol1 As Long
        lgDestCol1 = convertLetterToLong(C_COL_LTR_NAME)
        
        '// If Server Type is blank,
        '// Erase Server Name
        If Target.Value = "" Then
        
            '// Blank out Name
            Cells(Target.row, lgDestCol1) = ""
        
        '// If not blank, set Server Name
        Else
        
            '// Get Vnet name
            Dim strVnetName As String
            strVnetName = Cells(Target.row, convertLetterToLong(C_COL_LTR_ENV))
            
            'Get Site Code
            '<site code>
            Dim strSiteCode As String
            strSiteCode = getDefaultSitecode( _
                            ActiveSheet.Name, _
                            Target.row)
        
            'Get Location
            '<location>
            Dim strLocation As String
            strLocation = Mid$(strVnetName, I_LEN_SITE_CODE + 1, nameVal.cStateCode)
            
            If strSheetName = C_SHEETNAME_CLOUD Then
            
                Dim strVnetCode As String
                strVnetCode = getVnetCode( _
                                strVnetName)
                
                Dim strSubnetCode As String
                strSubnetCode = getSubnetCode( _
                                    Cells(Target.row, convertLetterToLong(C_COL_LTR_SUBNET_OR_ZONE)), _
                                    strVnetCode)
                                    
                strSiteCode = strSiteCode & strLocation
                
            End If
                
            ''''''''''''''''''''''''''''''''
            '// Set Name Column
            ''''''''''''''''''''''''''''''''
            '// Get Function Code
            Dim existingRoleCode As String
            existingRoleCode = getFunctionCode(Target.Offset(0, 1).Value)
            
            '<function code>
            Dim strFunctionCode As String
            strFunctionCode = xlvLookup( _
                                C_SHEETNAME_LISTS, _
                                C_RANGENAME_LISTS_SERVER_ROLE, _
                                Target.Value, _
                                0, _
                                1)
            
            '<sub-site identifier>
            Dim strLkupValue As String
            strLkupValue = IIf(strSheetName = C_SHEETNAME_CLOUD, strSubnetCode, strVnetName)
            
            Dim subsiteID As String
            subsiteID = getSubsiteCode( _
                            ActiveSheet.Name, _
                            strLkupValue)
            
            '// If Server Role Name Changed, update Name
            '// Set Server Name
            Cells(Target.row, lgDestCol1) = _
                strSiteCode & _
                strFunctionCode & _
                C_SERIAL_NUM_PATTERN & _
                subsiteID
            
            '// If server type "other" flag set server type background color
            If Cells(Target.row, Target.Column) = "Other Server" Then
                Call setTextRed(Target.row, Target.Column)
            Else
                Call setTextNormal(Target.row, Target.Column)
            End If
            
            '// Set Background color to red if serial num = ### (the auto-generation default)
            '// Otherwise remove background color
            Dim vTargetCell As String
            vTargetCell = C_COL_LTR_NAME & Trim(Str(Target.row))
            
            Dim vTarget As Excel.Range
            Set vTarget = Range(vTargetCell)
            
            If InStr(vTarget.Value, C_SERIAL_NUM_PATTERN) > 0 Then
                Call setTextRed(Target.row, Target.Column + 1)
            Else
                Call setTextNormal(Target.row, Target.Column + 1)
            End If
            
            
        End If
    
    
    CleanExit:
        
        Excel.Application.enableEvents = True
            
        On Error GoTo 0
        
    End Sub

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    You haven't recently added a variable or a subroutine called Max by any chance?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Quote Originally Posted by xld View Post
    You haven't recently added a variable or a subroutine called Max by any chance?
    After it stopped working, I wrote a getHighestNumber() function to do the same thing.

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Does that have a variable called Max? If so, rename it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    No variables or functions named Max or anything close to it for that matter.

  7. #7
    I’ve worked around the “Max” function issue. I’m more concerned about why the Cells method only works now when I’m stepping through the code using a break point.

  8. #8
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    829
    Location
    Maybe...
    strSheetName.Cells(Target.row, Target.Column) = ""
    HTH. Dave

  9. #9
    I tried:

    Dim xlWs as Excel.Worksheet
    Set xlWs = ThisWorkbook.Sheets(strSheetname)
    
    With xlWs
    
        .Cells(target.row, lgDestColl, etc.
    
    End With
    I set all of the code between the With/End With statements and used .Cells and not just Cells. That did not work either.

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Can you upload a copy of this workbook?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    I’ll have to remove some proprietary information first, and then send it.

Posting Permissions

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