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 :yes, 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.