-1

For VBA functions and most programming in general i understand general program execution to occur something like this...

Inside a sub procedure if you call a function (it should return a value), in this case it would return a value to variable num.

To expand this means that execution reaches the line where I call the function, and should then skip down to where the function is written, and go through executing each line inside that function.

This is how it has worked for me previously when I use the F8 key to highlight and follow the code execution line by line.

The problem The problem is when execution reaches the line inside the sub procedure where the function is called the function is just skipped over and execution doesn't go inside the function and run each line inside it.

(and I should say when this happened I had 95% of a working program, and i've tried re-writing the function, calling other functions).

But whenever a function is called execution of the code doesn't go into the function itself it just skips over it, and the variable that holds the result of the function therefore is left empty.

I've tried creating breakpoints at the beginning of the function and when the function is called in the sub procedure however this hasn't worked.

At the very least execution should get to the definition of the function (Function FirstRow() etc) and throw and error but it's not doing that.

Sub Main

    Dim num as double

    Dim sheet_name as string

    num = FirstRow(sheet_name)

End Sub

Function FirstRow(sheet as string) as double

    select case sheet '<<----- execution never gets inside the function

    case "sheet"
        FirstRow = 8
    case "sheet2"
        FirstRow = 12

    end select

End Function
yoshiserry
  • 20,175
  • 35
  • 77
  • 104
  • Sorry but your problem is not reproducible. When I run your sub, I get inside the function without problems. Could you please add a reproducible test case? – Matteo NNZ Apr 22 '15 at 22:24
  • I've tried putting just a string in the function when I call it, so there is a value in place of sheet name. Neither works. It just skips over the function. I can't provide an example workbook. – yoshiserry Apr 22 '15 at 23:05
  • If you cannot provide a reproducible example, I think you cannot get much help here because your code is supposed to work as you expect, and this is what it actually does on my computer. So either is a problem of your machine (try into another one), or you're omitting relevant part of code. – Matteo NNZ Apr 22 '15 at 23:14

2 Answers2

0

Put a value in sheet_name and try again.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • `sheet_name` is declared as string, which means it has a defalut value of `""` since compilation. Even if not assigned, there's no reason why it shouldn't get into the function (indeed, the code above runs straight in my computer). – Matteo NNZ Apr 22 '15 at 23:12
  • I agree I see no reason why execution shouldn't move inside the function I am stumped. – yoshiserry Apr 22 '15 at 23:25
  • @yoshiserry Are both the *Sub* and the *function* in the same standard module ?? – Gary's Student Apr 23 '15 at 00:33
  • they are actually all these functions, subs are inside a module which I get to by clicking on my import_form which I created. Is there a limit to the number of functions or procedures you can write when the functions subs are inside a module from a userform? – yoshiserry Apr 23 '15 at 01:12
0

What caused some functions to stop working (and execution of code to not reach the function definition or move inside it) was unloading the form.

All the functions and sub procedures were defined in a module which is part of the form.

Therefore you can only access this module by clicking on the controls on the form.

This meant that I only had one folder FORMS - which had one form inside it, and didn't have a Modules Folder which would normally appear in the VBA Project section.

Therefore I can either: 1) wait until all the data processing has been done before unloading the form right at the last minute

or

2) move the functions to a new module (External to the Form in the MODULES folder) so when the fuctions and sub procedures are called they don't rely on the form being open.

yoshiserry
  • 20,175
  • 35
  • 77
  • 104