Custom Function Not Working

  • Re: Custom Function Not Working


    Jonathan


    I took a look at your code and I think you might want to replace your subs with functions that return a value.


    Or change how you are declaring your variables.

  • Re: Custom Function Not Working


    I put this code into my VB editor and made it run via a command button--it works perfectly! So... in Excel I need to make all of these functions?? How would I call these "functions" in the code? I thought you had to define variable inputs with a function.


    I'm thinking of trying to ditch the subs and put all of the code in the single function. However, I'd still like to know if I can call subs from a function in excel. Thanks!

  • Re: Custom Function Not Working


    Jonathan


    As far as I know you should be able to call subs from a function, it just depends on what you are doing with the sub.


    If you are trying to do some sort of calculation then I think functions are the way to go.


    In my previous post what I was thinking about was the scope of the variables you dimmed.


    When I stepped through your code in the function Depth the variable Channel_Bottom had the value 2 at first but when it went to the Hydraulic_Radius sub it was 0.

  • Re: Custom Function Not Working


    Hmmm... I wonder why they aren't working? I put the code into a standard VB module and it works perfectly. I then tried changing the variables to "Public" just in case, and nothing happened. Is there something odd/specific to Excel? Is it because I'm using the module for the function, but the sheet is separate?

  • Re: Custom Function Not Working


    It's not working because you are passing arguments to functions that are beyond it's calling scope (Not initailized so = 0)


    eg


    Channel_Bottom * Flow_Depth > channel_Bottom =0 as does all your other variables ... > Division by Zeroe errors


    why not just (


  • Re: Custom Function Not Working


    Yup--that is exactly what I did (putting all of the code into the function). It works fine, but I'm still curious how I can use a function to call subs and carry the value of the variables. The reason I like doing this is to keep my code uncluttered. I generally like to have the "meat" of my code be a simple subroutine that calls other subs--that way I can see what order things should be happeening. And... if you saw my final code with all of the tables, constants, etc., you'd see why I like a sub that just calls other subs--it's a mess!


    Anyway, like I said, I put that sub into my VB editor and it worked *perfectly* as a *sub*--not a function. What is diff about a function, and how can I make it carry the constant values it grabs from my sheet to other subs?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!