VBA Function will not execute.

You should probably put code tags around your code (instructions are in the forum rules link at the top of the page) to make your code easier to read and copy.

What do you mean "it won't calculate" Does it return an error value? Remain unchanged? Usually when I have a UDF that "refuses to calculate" it's because of some syntax error in the function that is preventing it from compiling or running. A couple of things to look at:

1) What are the Range references doing? When you use the Range object unqualified like that, I think VBA assumes you are referring to the active sheet or the selection, which usually is not what you want with a UDF, and may be causing the function to hang.
2) UDF's should get the information they need from the spreadsheet through the argument list. I would suggest examining the goal of the UDF to see if, rather than passing colstrt as a string reference to the desired concatenation range, you can pass the entire range as an argument to the function. This is one way to eliminate any confusion on what object Range is referring to. It also could allow you to eliminate the application.volatile statement, as Excel would be able to see the dependencies and calculate the function only when needed.

BTW, How come I can say SUM(A1:A10) but I cannot say CONCATENATE(A1:A10)?!

That is a good question. My only answer is because the Excel gods/programmers have decreed it so. FWIW, I have seen mention of a UDF floating around the internet that is supposed to allow a range concatenate like that.

The scope of the concatenation is calculated.

It may be calculated, but it is calculated outside of the function and the results passed to the function as arguments. The idea is to change the code in such a way that you can specify in the argument list what range is being used for the concatenation. So, for example, one idea is to pass the main range to the function like this:

Please Login or Register  to view this content.

It was quickly put together, so it may still need work, and I think I'd probably take a different approach overall. But, in this version of the code, ccrange holds the reference to the range that you want to take the concatenation from. It is still using colstrt and numchar to determine exactly what subset of ccrange to concatenate, but ccrange will be a direct reference to the range desired, rather than an unspecified range. The other thing I hope it illustrates is that, just because something isn't known exactly at compile time, doesn't mean we shouldn't specify where that information will be stored and how it will be used.

In an overall sense, if I were writing this, I'd probably drop colstrt and numchar, and use the OFFSET() or INDIRECT() functions in the function call to determine the range needed, then pass that range to the msconcat function as the only argument. It should be able to work the same, unless I am completely misunderstanding what you are trying to do.

Originally Posted by MrShorty

That is a good question. My only answer is because the Excel gods/programmers have decreed it so. FWIW, I have seen mention of a UDF floating around the internet that is supposed to allow a range concatenate like that.

The 'decreed it so' for some good many years, now! I think it is not provided to discourage/prevent people from writing strong, open source, uninfectable cryptographic s/w like I did. :o)

2012.09.20...Almost VisiCrypt 1.2.xls

The file has detailed description of what needs to be done to demonstrate the problem on the opening tab.

I did not look at your suggestion yet, but I will. Thx.

Obviously, I haven't got the time nor inclination to go through the whole thing. But it does look to me like the error is in not specifying what Range(colref & coltop+i) refers to. Right now, it appears that mconcat is trying to pull data from S36 and below. If I put some text in S36 and below on main, then let the function run while my cursor is on main, then I will get the text from main instead of from calc. It still looks to me like, whether through my previous suggestion or some other method, you are going to need to somehow specify where VBA should always go to get the data from.

I'll try giving it the tab name in the first arg as well.

I'll post the results. Thx.

Originally Posted by MrShorty

...But it does look to me like the error is in not specifying what Range(colref & coltop+i) refers to. ...

Thx a google. I fixed it! All it wanted was to know on what tab s36 was on. I assumed it will use the tab that invoked it, but I was wrong! One has to specify the tab explicitly. Here's the code that works.

Please Login or Register  to view this content.

Glad you got it figured out.

The next scenario you might want to consider, because it will likely create a similar problem is when you have 2 workbooks open. If you have 2 workbooks open, and the "other" workbook is active, then it may look for the "calc" tab in that workbook. If it finds it, it will likely have the wrong data, and, if that worksheet doesn't exist in that book, it will error out.

Originally Posted by MrShorty

...it will likely create a similar problem is when you have 2 workbooks open...

I changed the call to be: '[filename]tab'!col

Note the single quotes within. It doesn't work w/o them!