Unable to create simple VBA function in Excel Macro and call it

Question

I'm trying to create a macro that can collect data from an excel spreadsheet in the local active workbook and then create header file which I would later incorporate into my project. But for the life of me I must be missing something so DUMB that I can't create a working function that returns a string (which would construct a C++ structure) to the calling function. I've simplified the example code to is absolute bare minimum to isolate the problem but I still cannot figure out what I'm doing wrong. I'm not an expert at VBA but I know how to create code and I can't narrow down what VBA is unhappy about. I keep getting "compile error, syntax error." Please copy the following code into your module and see if compiles properly for you. If you know where I went wrong please let me know. Much Appreciated!!!

Sub CREATE_FACTORY_SETTING_HEADER()
    Dim FS, TSsource
    Set FS = CreateObject("Scripting.FileSystemObject")

    Dim TSout
    Set TSout = FS.Createtextfile("HeaderFile.h", True)

    Dim fileHeading As String
    fileHeading = "File Heading for Header file"

    Dim fileBody As String
    fileBody = "Some initial file body lines"

    fileBody = fileBody & createStructBody

    TSout.Write fileHeading & fileBody
    TSout.Close    
End Sub


Public Function createStructBody() As String
    Dim structBody As String
    structBody = "Hey I'm a struct body, but I can't be returned for some     reason"
    Return structBody
End Function

You need to specify WHAT and WHERE your errors are. Which lines of code cause what errors? — Brian M.
– Brian M., Commented Jun 24, 2015 at 17:25

Ekkehard.Horner · Accepted Answer · 2015-06-24 17:49:56Z

5

Both VBA and VBScript use 'assignment to function' (instead of 'return' or 'result of last statement') to return results from functions. So

Public Function createStructBody() As String
    createStructBody = "Hey I'm a string and can be returned."
End Function

answered Jun 24, 2015 at 17:49

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to create simple VBA function in Excel Macro and call it

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related