One-button import of VBA module to Excel

Question

One-button import of VBA module to Excel

Mark Travis 30

I have a website that helps a user create a specialized mathematical formula. The website then builds an Excel Custom Function in Visual Basic. I've had my users cut and paste the VB code from a dialog on my website to the Developer Section of Excel by "Insert Module.." followed by pasting the VB code and saving it.

My users are about as error prone as you can get. They cut and paste incompletely, or they will have several Excel sheets open and import the VB in to the wrong worksheet. I need to come up with something stupid simple.

Ideally, I'd like to have one button in the ribbon bar that represents a way to import a "customfunction.bas" into an Excel workbook without them having to get involved with the "Developer" area at all. (They are data scientists, not programmers.)

I've found VB code that triggers a dialog box to open a file, but I can't seem to string together the rest of the logic to make this happen. Any hints??

Sub ImportVBAToCurrentSheet()

Dim FileName As String

FileName = Application.GetOpenFilename()

MsgBox FileName

End Sub

Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff

2023-05-30T08:07:42.3766667+00:00

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Accepted answer

0 additional answers

Your answer

Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff

2023-05-30T08:07:42.3766667+00:00

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 1

Tanay Prasad 2,250

Hi,

You can use the following approach:

Create a new Excel workbook (let's call it "Import Custom Function.xlsm") and add a custom ribbon tab with a single button. This button will trigger the import process.
In the Visual Basic Editor, create a new module in the "Import Custom Function.xlsm" workbook.
In the module, write the code that will handle the import process. Here's an example code snippet to get you started:

Sub ImportVBAToCurrentSheet()
    Dim FileName As String
    Dim VBModule As Object
    
    FileName = Application.GetOpenFilename("VB Files (*.bas), *.bas")
    
    If FileName <> "False" Then
        Set VBModule = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
        VBModule.CodeModule.AddFromFile FileName
        MsgBox "VBA module imported successfully!", vbInformation
    End If
End Sub

Save the "Import Custom Function.xlsm" workbook with the custom ribbon tab and module.
Distribute the "Import Custom Function.xlsm" file to your users.

When users open the "Import Custom Function.xlsm" workbook, they will see the custom ribbon tab with the button. Clicking on the button will trigger the ImportVBAToCurrentSheet macro. It will prompt the user to select a .bas file containing the VBA code. Once a valid file is selected, the code will create a new module in the current workbook's VBA project and import the code from the selected file. A message box will confirm the successful import.

I hope this works!

Best Regards.

Mark Travis 30 Reputation points

2023-05-30T16:24:30.59+00:00

Thanks! This is great. I am, however, getting an error that "vbext_ct_StdModule" is not defined.

I see here that this is a standard module definition. https://learn.microsoft.com/en-us/office/vba/language/reference/visual-basic-add-in-model/properties-visual-basic-add-in-model

Why am I getting an error?

I'm using Office 365 for Mac and I'm at the latest release (16.73). It says Microsoft Visual Basic for Applications 7.1 is the installed version.

Tanay Prasad 2,250

The error you're encountering is because the constant vbext_ct_StdModule is not available in the Mac version of Office.

In Office for Mac, the constant value for a standard module is different. You can replace vbext_ct_StdModule with the value 1 to specify a standard module.

Here's the modified code that should work on Office 365 for Mac:

Sub ImportVBAToCurrentSheet()
    Dim FileName As String
    Dim NewModule As Object
    Dim NewCode As String
    
    ' Open file dialog to select the VBA file
    FileName = Application.GetOpenFilename("VBA Files (*.bas), *.bas")
    
    ' Check if a file is selected
    If FileName <> "False" Then
        ' Read the contents of the VBA file
        Open FileName For Input As #1
        NewCode = Input$(LOF(1), 1)
        Close #1
        
        ' Add a new module to the active workbook
        Set NewModule = ActiveWorkbook.VBProject.VBComponents.Add(1) ' Use 1 for a standard module
        
        ' Insert the VBA code into the new module
        NewModule.CodeModule.AddFromString NewCode
        
        ' Display a success message
        MsgBox "Custom VBA code imported successfully!", vbInformation
    End If
End Sub

Let me know if this works.

Best Regards.

Mark Travis 30 Reputation points

2023-05-31T17:26:36.6366667+00:00

That worked with the exception of putting "VBA Files (*.bas), *.bas" as a parameter to GetOpenFilename. I get Run-time error '1004': Method 'GetOpenFilename' of object '_Application' failed

If I just write it as GetOpenFilename() it works, but it lists all files, not just *.bas files.

Thank you!!

The disparity between Windows and Mac Office 365 is a thorn in my side that I didn't even know existed. Is there a good place to read about these types of differences between the platforms? This isn't my main area of expertise. I generally live in the c++ world between Linux and Mac. It took me a month to get the VBA download code written, debugged, and ready for production.
Tanay Prasad 2,250 Reputation points

2023-06-02T06:36:46.52+00:00

I'm glad your issue is resolved.

As for a good place to read about this stuff, you can always refer to Microsoft Office Support, Blogs, Community, and other Forums like Stackoverflow or Reddit. You can always ask your doubts here and there and you will get your answers. ;)

If you feel like my answers helped you, I'd appreciate it if you can mark it as Solved or Accepted Answer.

Share via

One-button import of VBA module to Excel

0 additional answers

Your answer