Why is my VBA script exiting early?

Hey all, I am interning and I have a VBA script that is linked to "Thisworkbook" which is as follows

Please Login or Register  to view this content.

when I step through the code to analyze it the code works fine about 90% of the time however the other 10% of the time the code gets to the line of code that trims the B column and it just leaves this script and goes into the code I have in my inserted module which is

Please Login or Register  to view this content.

This function exists in none of the columns that are being evaluated by the program so I am unsure why its being called and why the program is leaving the original macro that I am stepping through. Am I missing something in my code that should make this happen? I unfortunately cant attach the file due to confidentiality reasons.

I think I have created and attached a file, not 100% sure on the attachment. For some reason I cant even step through the macro that works on workbook open on this test file. I changed nothing from the code

I check if the cells in the columns after B are empty mostly because in the real file I have some of the locations that have a blank value some specific rows. when I first ran the file it would sometimes get to the line nochar/string and the program would absolutely freeze. I was assuming that the program was trying to do something similar to dividing by zero so I put those lines in and it seemed to fix that issue. I only trim B because the templates are user created and sometimes people add spaces that we dont want. I didn't trim the other columns because I want to preserve and signal any errors that may be in the rows so those individual locations can fix them in their personal file locations As to the question someone else asked of what I want to achieve, as I had stated before this code runs fine about 90% of the time and then about 10% of the time when I run it, excel freezes and crashes. When I try to figure out why by stepping through the program it randomly exits the macro at the trim line and enters the lines of code for the getcolor function. I am assuming this is what is causing the crash, my end goal is to understand why excel is randomly exiting the macro early/or figure out why about 10% of the time running the code causes excel to absolutely crash. For reference in the 90% of the time it runs fine, the program takes about 20 seconds to run. In the 10% that it fails there have been instances where ive left if for 10 minutes before touching it.

I added Application.Calculation = xlCalculationManual at the beginning of the code and Application.Calculation = xlCalculationAutomatic at the end and ran the program again. Once I clicked the run button excel instantly went to not responding. My issue persists.

520 cells and it runs twice per column, 9 columns per sheet, for 5 sheets. What is the flaw?

Sintek, post 4 has been addressed. If you needed more specific information than is present in post 7 you'll have to clarify.

Originally Posted by Sintek

Please explain what it is you are actually wanting to achieve...

my end goal is to understand why excel is randomly exiting the macro early/or figure out why about 10% of the time running the code causes excel to absolutely crash.

It has been addressed. I indicated that I am looking more for a possible answer to why the macro is randomly exiting, you are trying to help me with a different task, coding the problem. I am not looking for anyone here to write code for me, thats lazy. I am looking for an answer to a question of why is excel behaving this way with the given code.

Originally Posted by romperstomper

Looking at your code, I think you'd be better off having columns with functions for the similarity scores, then use conditional formatting to colour the cells. Your totals functions can then use the values in the additional columns to calculate, and they will always be accurate.

doing this seems to have fixed it, not sure why the function would interact with the macro, but either way deleting the function seemed to fix it. Thank you for your help.