+ Reply to Thread
Results 1 to 11 of 11

VBA Function will not execute.

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Vancouver, BC, Canukistan
    MS-Off Ver
    12
    Posts
    21

    VBA Function will not execute.

    I have a simple VBA function to concatenate a variable number of characters. Here's what it looks like:

    Please Login or Register  to view this content.
    When I go to use it in the main spreadsheet, it WILL NOT calculate unless I select the cell where it is used, place cursor at end of the equation and reenter it again. Once I do that, it calculates corrently!

    Any ideas anyone? Thx. in advance!
    Last edited by danleonida; 09-19-2012 at 06:07 PM. Reason: codetags

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,448

    Re: 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.

  3. #3
    Registered User
    Join Date
    08-14-2012
    Location
    Vancouver, BC, Canukistan
    MS-Off Ver
    12
    Posts
    21

    Re: VBA Function will not execute.

    Quote Originally Posted by MrShorty View Post
    You should probably put code tags around your code...
    I'll do it in the future. Sorry!

    Quote Originally Posted by MrShorty View Post
    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.
    It returns zero! There is no syntax error since it calculates OK if I select one of its cells, place cursor at the end of the equation and hit 'enter'.

    The UDF concatenates a portion of a column. The first arg, COLstrt, is the first cell and NUMchar is the number of cells down that need concatenation. I cannot be any more specific because the range of cells is only available at runtime. BTW, How come I can say SUM(A1:A10) but I cannot say CONCATENATE(A1:A10)?!

    Quote Originally Posted by MrShorty View Post
    When you use the Range object unqualified like that, I think VBA assumes you are referring to the active sheet or the selection, ...
    Nothing is known at compile time! Everything is only known during execution.

    Quote Originally Posted by MrShorty View Post
    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.
    As I said before, The scope of the concatenation is calculated.
    Last edited by danleonida; 09-19-2012 at 06:02 PM. Reason: grammar/

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,448

    Re: VBA Function will not execute.

    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.

  5. #5
    Registered User
    Join Date
    08-14-2012
    Location
    Vancouver, BC, Canukistan
    MS-Off Ver
    12
    Posts
    21

    Re: VBA Function will not execute.

    Quote Originally Posted by MrShorty View Post
    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.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,448

    Re: VBA Function will not execute.

    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.

  7. #7
    Registered User
    Join Date
    08-14-2012
    Location
    Vancouver, BC, Canukistan
    MS-Off Ver
    12
    Posts
    21

    Re: VBA Function will not execute.

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

    I'll post the results. Thx.

  8. #8
    Registered User
    Join Date
    08-14-2012
    Location
    Vancouver, BC, Canukistan
    MS-Off Ver
    12
    Posts
    21

    Re: VBA Function will not execute.

    Quote Originally Posted by MrShorty View Post
    ...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.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,448

    Re: VBA Function will not execute.

    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.

  10. #10
    Registered User
    Join Date
    08-14-2012
    Location
    Vancouver, BC, Canukistan
    MS-Off Ver
    12
    Posts
    21

    Re: VBA Function will not execute.

    See what you mean!

    I'll change it to include the filename as well. BTW, I changed the macro to make it more general pupose:

    Please Login or Register  to view this content.
    Note I changed the name not to have it confused with Microsoft's!

    Thx again.

    Any interest on this forum in cryptography?

    I'm trying to start a dialogue about VisiCrypt on Wilders Security forum. There are more links there to s/w, viruses, performance screenshots, etc.
    Last edited by danleonida; 09-20-2012 at 04:24 PM. Reason: add

  11. #11
    Registered User
    Join Date
    08-14-2012
    Location
    Vancouver, BC, Canukistan
    MS-Off Ver
    12
    Posts
    21

    Re: VBA Function will not execute.

    Quote Originally Posted by MrShorty View Post
    ...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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1