Can you make your main routine be a Function? and related questions from a n00b w C++ background

[nicolesherburne]

Hi everyone!

I am learning macros in Excel so I can do some things at work. Starting from zero, with a light background in C++ and UNIX shell scripting.

One thing I like about C++ and sh are that you can do error trapping because everything is a function. I see that in Excel vbs there are subs and functions, and they are different. I have read the following:

Understanding Functions in Macros by Allen Wyatt
https://excel.tips.net/T002259_Understanding_Functions_in_Macros.html

Excel: Subroutines and Functions
http://excel.officetuts.net/en/training/subroutines-and-functions

Can a Subroutine be called within a Function? post on ozgrid forum
https://www.ozgrid.com/forum/forum/...-can-a-subroutine-be-called-within-a-function

The last reference there has an interesting exception to the rule that a function can't modify the contents of other cells.

Function MyFunc()
    Call MySub
End Function


Sub MySub()
    MsgBox "hello" 'OK
    Range("A1").Value = 1 'Not
End Sub

I'm sure there's a good reason, but why don't macro programmers write everything as a function and then only use little subs when it's necessary to actually write something to a cell?

By doing that you could have all your functions return error codes so your main function would know whether the function it called was successful. We used that for error trapping in computer science class.

Anyway, hi, thanks in advance for any discussion this generates. I'm interested to learn more about this language and figure out the dos and don'ts.

~Nicole

 

[Scott Huish]

That code will actually work, and this has been the subject of much debate.
Generally, I write functions when it needs to return a value, and macros (subs) when it needs to do anything else.

 

[nicolesherburne]

Thank you very much, Scott!

In this case, I think I'm going to default to using functions for pretty much everything except what needs to change the values of cells, in which case it would be a sub.

I just wanted to make sure I wasn't going to go down a path that led to perdition, but if it's a legitimate style choice I think I'll be more comfortable thinking of it in ways I'm already used to from my background in C++ and scripting.

I really appreciate your response! Great to meet you and have a wonderful day

~Nicole

That code will actually work, and this has been the subject of much debate.
Generally, I write functions when it needs to return a value, and macros (subs) when it needs to do anything else.