0

code:

Function getValue(pathname, filename, sheet, rng)

If Right(path, 1) <> ":" Then path = path & ":"

Dim rngR1C1, ref As String:
rngR1C1 = Range(rng).Address(, , xlR1C1)

ref = "'" & pathname & "[" & filename & "]" & sheet & "'!" & rngR1C1

getValue = ExecuteExcel4Macro(ref)

End Function
Community
  • 1
  • 1
dkajfdksajfhlas
  • 1
  • 1
    1) Define your variables as explicit types. 2) Also `path` is not defined anywhere so when called from cell it will break. – Scott Holtzman Oct 07 '17 at 02:16
  • I've tried declaring them all as strings but then it breaks even in the subroutine. It gives "Compile error: ByRef argument type mismatch." even though I declared all of my variables in the sub as strings. Also path was a typo it should've said pathname – dkajfdksajfhlas Oct 07 '17 at 02:22
  • Type mismatch error comes because you did not pass the right. Make sure you pass a string literal for `pathname`, `filename`, `sheet` and a range object (i.e. - A1) for `rng`. Or you can change your code and pass range as `String` – Scott Holtzman Oct 07 '17 at 02:29
  • Using string literals gives the same error but it works if I use byval? Either way it doesn't work in a cell formula. Also what's wrong with using a string for rng in my code? – dkajfdksajfhlas Oct 07 '17 at 02:46
  • There is nothing wrong with using `rng` as String. That was my mistake. After reviewing with fresh eyes I realized that the problem is `ExecuteExcel4Macro` will not work when called from cells. See this [Q&A](https://stackoverflow.com/questions/29521245/a-function-within-a-function-in-vba) for help. (Make sure to check out links in 2nd comment of question as well. – Scott Holtzman Oct 09 '17 at 13:44

0 Answers0