Excel Cells and VBA MAX Functions Stopped Working

[OldSchool48]

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.

[OldSchool48]

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

[Bob Phillips]

You haven't recently added a variable or a subroutine called Max by any chance?

[OldSchool48]

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.

[Bob Phillips]

Does that have a variable called Max? If so, rename it.

[OldSchool48]

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

[OldSchool48]

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.

[Dave]

Maybe...

strSheetName.Cells(Target.row, Target.Column) = ""

HTH. Dave

[OldSchool48]

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.

[Bob Phillips]

Can you upload a copy of this workbook?

[OldSchool48]

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