VBA Excel code working if called by button but not if called by function

Question

I'm trying to open a file using a function. The same code is working if I call it by a button. The file will open, I can modify it and I can close it without saving. But if I use the same code called by the function the file will not be open. Why? Following the code used with the button and with the function. Might anyone help me? This is only the beginning of what I would need. Detailing, with this function I need to open a CSV file, fix the CSV file, extract a data with a Dlookup and write this data on the cell where I called the function. This works if I call my code by button but not if I call it with a function in a cell

Sub Button1_Click()

Dim path As String
Dim xl As Excel.Application
Dim wk As Excel.Workbook

path = ActiveWorkbook.path & "\Book1.xlsm"

Set xl = Excel.Application
Set wk = xl.Workbooks.Open(path)
    xl.Visible = True
    wk.Activate
    xl.DisplayAlerts = False

Range("B2").Select
Range("B2").Value = "hello world"

wk.Saved = False
wk.Close    
  Set wk = Nothing
End sub

Public Function aprifile()
Dim path As String
Dim xl As Excel.Application
Dim wk As Excel.Workbook

path = ActiveWorkbook.path & "\Book1.xlsm"

Set xl = Excel.Application
Set wk = xl.Workbooks.Open(path)
    xl.Visible = True
    wk.Activate
    xl.DisplayAlerts = False

Range("B2").Select
Range("B2").Value = "hello world"

wk.Saved = False
wk.Close    
  Set wk = Nothing
End Function

How are you calling the Function? From a cell (not likely to work) or from within a VBA routine? — CLR, Sep 13 '18 at 10:26
If you are calling this from the worksheet then you cannot change the value of any other cell than the one containing the function. — , Sep 13 '18 at 10:36
@DisplayName detailing my need, with this function I need to open a CSV file, fix the CSV file, extract a data with a Dlookup and write this data on the cell where I called the function. This works if I call my code by button but not if I call it with a function in a cell. — pa790, Sep 13 '18 at 14:05
yes, but what _triggers_ the need of doing that? maybe the editing of a cell? — DisplayName, Sep 13 '18 at 15:45
@DisplayName yes. I want the result in the cell where I call the function. — pa790, Sep 17 '18 at 13:34

score -1 · Answer 1 · answered Sep 13 '18 at 11:12

There's a couple of things here.

First I have made the thing a Sub rather than a Function as you're not returning anything.

Option Explicit

Public Sub aprifile()

Dim path As String Dim xl As Application Dim wk As Workbook

path = ActiveWorkbook.path & "\Book1.xlsm" If Len(Dir$(path)) > 0 Then

Set xl = Excel.Application
Set wk = xl.Workbooks.Open(path)
xl.Visible = True
wk.Activate
xl.DisplayAlerts = False

wk.ActiveSheet.Range("B2").Select
wk.ActiveSheet.Range("B2").Value = "hello world"

wk.Saved = False
wk.Close
Set wk = Nothing

End If End Sub

The second thing is that I placed 'wk.ActiveSheet' to make sure that I am writing to the correct sheet. Without that I found that I was writing to the wrong workbook.

The other thing I did, as you can see, I checked to see if the file existed. I found that for my first run it didn't so that may give you a clue there.

But, I can assure you that this code works because I've just done it here.

Hope that this helps Malc

generaly it's better to avoid things like activesheet, better adress by name — Wouter, Sep 13 '18 at 12:12
Yes, I know it's better to refer to the pointed sheet, but here I was just giving a quick example off the cuff. But, yes, you;re dead right and I normally would have done it that way but failed to do it correctly. Have a +1 from me. — Shoeless, Sep 13 '18 at 20:06

VBA Excel code working if called by button but not if called by function

1 Answers1