I suspect you haven’t set up last_name properly in the caller. With the statement Worksheets(data_sheet).Range(“C2”).Value = ProcessString(last_name) this will only work if last_name is a string, i.e. appears in the caller somewhere. The reason for this is that VBA passes in variables by reference by default which means that the data types have to match exactly between caller and callee. Two fixes: … Read more
I use and recommend Rubberduck. Description from the website: Rubberduck is a very active open-source COM add-in project that integrates with the Visual Basic Editor to enable the features every programmer wants to have in their IDE. From unit testing to source control, from code inspections to refactorings, programming in VBA will never be the same.
On the line with the first If, you must go to a new line after Then, otherwise your If will be implicitely closed.
I’m a beginner and trying to create a formula that modifies the contents of Cell A1 based on the color of the cell in B2; If Cell B2 = [the color red] then display FQS. If Cell B2 = [the color yellow] then display SM. This is conditional based on the cell fill color.
Below is a solution from here
I’ve found a workaround. At first it seemed it would take up more time, but it actually makes everything work smoother and faster due to less swapping and more memory available. This is not a scientific approach and it needs some testing before it works. In the code, make Excel save the workbook every now … Read more
I’m not aware of a shortcut for this. Here’s what I do: Ctrl+End to go to the last row. Arrow over to the intended column. Ctrl+Up once or twice to select the column starting from the bottom.
For non-object return types, you have to assign the value to the name of your function, like this: Example usage: If the function returns an Object type, then you must use the Set keyword like this: Example usage: Note that assigning a return value to the function name does not terminate the execution of your function. If … Read more
I assume that you’re writing to the file, then close it (so the user can open it in Excel), and then, before re-opening it for append/write operations, you want to check that the file isn’t still open in Excel? This is how you could do that:
Thanks for everyone’s contribution. It turns out that the issue was caused by Copying the entire sheet would sometimes deplete my systems memory and cause the copy function to fail. For those interested, I simply selected a smaller range. This fixed the problem