VBA Works for Me But Not Others

[jessebh2003]

I have two macros each with an error.

The first macro has a very long formula that works correctly for me but when others run the macro, they get an error message. Here's the formula:

=COUNT(FILTER(ROW(Data!$AQ$3:$AQ$1000),(Data!$AQ$3:$AQ$1000="Yes")+(Data!$AQ$3:$AQ$1000="No")+(Data!$BA$3:$BA$1000="Yes")+(Data!$BA$3:$BA$1000="No")+(Data!$BK$3:$BK$1000="Yes")+(Data!$BK$3:$BK$1000="No")+(Data!$BU$3:$BU$1000="Yes")+(Data!$BU$3:$BU$1000="No")+(Data!$CE$3:$CE$1000="Yes")+(Data!$CE$3:$CE$1000="No")+(Data!$CO$3:$CO$1000="Yes")+(Data!$CO$3:$CO$1000="No")+(Data!$CY$3:$CY$1000="Yes")+(Data!$CY$3:$CY$1000="No")+(Data!$DI$3:$DI$1000="Yes")+(Data!$DI$3:$DI$1000="No")+(Data!$DS$3:$DS$1000="Yes")+(Data!$DS$3:$DS$1000="No")+(Data!$EC$3:$EC$1000="Yes")+(Data!$EC$3:$EC$1000="No")+(Data!$EN$3:$EN$1000="Yes")+(Data!$EN$3:$EN$1000="No")))

In the VBA, the code is structured:

Range("J9").Select
ActiveCell.FormulaR1C1 = "=COUNT(FILTER(ROW(Data!R3C43:R1000C43),(Data!R3C43:R1000C43=""Yes"")+(Data!R3C43:R1000C43=""No"")" & _
"+(Data!R3C53:R1000C53=""Yes"")+(Data!R3C53:R1000C53=""No"")+(Data!R3C63:R1000C63=""Yes"")+(Data!R3C63:R1000C63=""No"")" & _
"+(Data!R3C73:R1000C73=""Yes"")+(Data!R3C73:R1000C73=""No"")+(Data!R3C83:R1000C83=""Yes"")+(Data!R3C83:R1000C83=""No"")" & _
"+(Data!R3C93:R1000C93=""Yes"")+(Data!R3C93:R1000C93=""No"")+(Data!R3C103:R1000C103=""Yes"")+(Data!R3C103:R1000C103=""No"")" & _
"+(Data!R3C113:R1000C113=""Yes"")+(Data!R3C113:R1000C113=""No"")+(Data!R3C123:R1000C123=""Yes"")+(Data!R3C123:R1000C123=""No"")" & _
"+(Data!R3C133:R1000C133=""Yes"")+(Data!R3C133:R1000C133=""No"")+(Data!R3C144:R1000C144=""Yes"")+(Data!R3C144:R1000C144=""No"")))"

We tried connecting all of the rows of code into one long row but got the same error.

The second macro replaces merge fields in Word with data from Excel, which all works correctly until the macro gets to the code to save the Word document.

.SaveAs2 Environ("userprofile") & "\Desktop\ROL Evaluation Report" & "_" & Sheets("Data").Range("B1").Value _
& "_" & Format(Now, "yyyy-mm-dd hh-mm") & ".docx"

The code works find for me but they get the error:
"Run-time error '5152':

This is not a valid file name.
Try one or more of the following:
*Check the path to make sure it was typed correctly.
*Select a file from the list of files and folders."

Can someone help me solve these two errors? Thanks!!!

 

[Joe4]

Functions are just VBA code that returns a value, rather than performs actions like Sub procedures do.
All you need to do is copy his function into your VBA module, exactly as-is.

Then you can use it either on the worksheet or in your VBA code like any other native function.

So in your code, you can check before saving the file like this:

If IsAlphaNumeric(Range("B1")) Then
'   your save steps here
Else
    MsgBox "You have invalid characters in cell B1.  Please try again."
    Exit Sub
End If

 

[jessebh2003]

Functions are just VBA code that returns a value, rather than performs actions like Sub procedures do.
All you need to do is copy his function into your VBA module, exactly as-is.

Then you can use it either on the worksheet or in your VBA code like any other native function.

So in your code, you can check before saving the file like this:

If IsAlphaNumeric(Range("B1")) Then
'   your save steps here
Else
    MsgBox "You have invalid characters in cell B1.  Please try again."
    Exit Sub
End If

Ah, I see.

The Excel macro is populating a Word template. When they run the macro and if the cell has an invalid character, will the macro finish running after the cell is corrected? Sorry for all the questions, macros are still fairly new to me and I'm kind of learning as I go. Thanks!

 

[Joe4]

No, the "Exit Sub" statement kicks them right out of the VBA code, and that would have to start over.

There are a few different ways you can go about this:
1. Prompt them for a new entry in the VBA code if the existing one is bad, and check that one (loop until a good one is entered).
2. Use "Data Validation" on cell B1 that will only allow them to enter valid values. See here: How to only allow alphanumeric characters entry in Excel?
3. Use VBA code that runs automatically on entry if data in cell B1.

Option 1 will require a bit more coding.
Option 2 requires no VBA coding at all, but Data Validation can be lost if people copy a cell from somewhere else and paste over cell B1.
Option 3 is my preferred method, as it controls it upon data entry, and they cannot override it or bypass it unless the disable VBA (in which case, none of your code would work anyway).

Option 3 uses "Event Procedure" code, which is VBA code that runs automatically upon some event happening (like the updating of a certain cell). However, for it to run automatically, it has to follow a few rules:
1. It MUST be placed in the correct place, that is the proper "Sheet" or "Workbook" module. It cannot go in a Standard/General module (like "Module1").
2. It MUST be named a certain way. You cannot change the name of the procedure in any way.

So, here is code that will use that function that Rick wrote to check the value of B1 as they enter data, and give them an error right away upon entty.
The easiest thing to do is to go to the sheet that has this B1 value that will be updated, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste the following code in the blank VB Editor window that pops up:

Function IsAlphaNumeric(S As String) As Boolean
  IsAlphaNumeric = Not S Like "*[!a-zA-Z0-9.+-]*"
End Function


Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if cell B1 updated
    If Not Intersect(Target, Range("B1")) Is Nothing Then
'       Check to see if value is not alphanumeric
        If IsAlphaNumeric(Range("B1")) = False Then
            MsgBox "Entry in B1 contains illegal characters. Please try again.", vbOKOnly, "ENTRY ERROR!!!"
            Application.EnableEvents = False
            Range("B1").ClearContents
            Application.EnableEvents = True
        End If
    End If
        
End Sub

Then, just test it out!