-2

I am using VBA in Excel (Office 365) on a Windows machine. (I already discovered that virtually none of the main VBA functions work on a Mac).

Firstly, my apologies to all those who say "This question has already been asked". Maybe so, but I have just spent over an hour here and can't find the answer.

I found out how to pass the valuable of a variable TO a function but I just can't do it the other way, from function back to subroutine.

There are some similar questions but the code in the answers is way, way, way beyond my absolute beginner level, and I can't figure it out. I've tried loads of things like using "Option Explicit", "Dim", "Public" and "Private", but I just can't make this code work.

So here's the code. Please can someone re-write it correctly for me?

Sub GetTheValueOfXXX()

      getXXX
      MsgBox XXX
End Sub

Function getXXX()

      XXX = 999
End Function

Thanks to whoever takes pity on me as a complete newbie, and apologies to those who think this is like adding 1 plus 1.

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
user54752
  • 11
  • 1

1 Answers1

0
Sub GetTheValueOfXXX()
    Dim x
    x = getXXX()
    Msgbox x
End Sub

Function getXXX()
    getXXX = 999   '<< return a value using the function's name...
End Function

Array:

Sub GetTheValueOfXXX()
    Dim x
    x = getXXX()
    Msgbox x(0)   '>> 999
    Msgbox x(1)   '>> 88
End Sub

Function getXXX()
    getXXX = Array(999, 88, 7) 
End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thankyou Tim Williams! I see the differences. Your code works. However, it does not quite solve the problem in the way I hoped. I simplified the problem too much I think. You see I am trying to pass the value of 26 variables back ("AAA" "BBB", "CCC",...."ZZZ"). According to your code, it looks like I would have to write 26 functions to do this. Is there an easy way to load 26 variables in a single Sub or Function, and pass them all back for use in the main sub? – user54752 Jul 17 '18 at 05:34
  • See my edit above – Tim Williams Jul 17 '18 at 06:07