Function causing a little problem

[omairhe]

Hey all,

This function will allow only the alphanumeric characters when applied to a cell. However with new workbooks all goes well but with my old workbook the very function will cause the workbook to prompt user to save file, even though no changes are made to it. This can be a little annoying and I would like to make it go away. It is very clear that the function is causing this problem as removing it fixes the issue.

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

Will appreicate a lot.
Thanks.

 

[nemmi69]

How does this get called?

 

[Joe4]

What is the file extension of the old workbook?

 

[omairhe]

What is the file extension of the old workbook?

The file extension is .xlsb
I tried to recreate the problem but without any luck. If I copy move these sheets to new workbook then yes the problem is re-created.

 

[omairhe]

How does this get called?

The formula something like =AlphaNumericOnly(C10&D10&E10&F10) . These cells are all values except for C10 which referes to the value in the pivot table of the same row. I have 32 instances of this formula running across multiple sheets.

 

[omairhe]

I'd like to add one more thing. I deleted all vba codes except for the function. Then I deleted every other cell's content so that only the formula of that function remained in the sheets. Yet the problem will only go away if I either remove the function itself from the vba or remove the function's formulas altogether in the sheets. At first I was not aware what was causing the save prompt so I started taking apart the workbook.

 

[Joe4]

I cannot recreate the problem. If I add this code to a new module, and use the function in formula on multiple sheets, and save as an "XLSB" file, I am able to open and close the file without being prompted to save if I have not made any changes.

It makes me think that there is something else going on (or interacting with this code).
Do you have any event procedure VBA code in any of your sheet modules or in the Worbook module?
If so, what does that code look like?

 

[omairhe]

I cannot recreate the problem. If I add this code to a new module, and use the function in formula on multiple sheets, and save as an "XLSB" file, I am able to open and close the file without being prompted to save if I have not made any changes.

It makes me think that there is something else going on (or interacting with this code).
Do you have any event procedure VBA code in any of your sheet modules or in the Worbook module?
If so, what does that code look like?

I copied all the codes to my new workbook and still nothing. The problem occured all of a suden when I used calculate option.
Last thing I remember is using Application.Calculation = xlAutomatic when workbook is opened and closed. Afterwards it keeps prompting me as though I was still running it but infact I removed the script.
I am running Application.Calculation = xlManual on workbook open code . and it is still prompting me to save the workbook.

 

[omairhe]

Ok seems like I finally found the culprit. It turns out that there were two formulas in the workbook. And to give me the undesirable save prompt, both these formulas needed to be running side by side in the same workbook. If either one of these are missing from the workbook, then the workbook will close without prompting as it normally does.\

First one is
=IF(OR(M1="(All)",M1=""),"",OFFSET(A1,-1,0)+1)

and the second one is
=AlphaNumericOnly(C2)

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

and the error is recreated. could someone please guide as to what alternative I have for the first formula as that will do the auto numbering for me.

 

[Joe4]

First one is
=IF(OR(M1="(All)",M1=""),"",OFFSET(A1,-1,0)+1)

and the second one is
=AlphaNumericOnly(C2)

Where exactly have you placed these formulas?
In which cells?
Can you post a small sample of your data that contains the cells that these formulas reference?