Unable to change cells in globally declared Workbook - Why?

[LazyJay]

Hi, I'm experiencing problems with a globally declared Workbook: I seem to be able to extract data from it but every time I try to change a value in this workbook, the Function suddenly Exits without any error message. Any help would be greatly appreciated!!!

All my code is in a Module. I declare my variable at the top of the module with:

Public wbDataFile As Workbook

Then there is a Sub which sets the variable as follows:

Set wbDataFile = Workbooks.Open(strFullImportPath)

Now, the workbook in wbDataFile is completely empty (except a few strings for testing purposes. ThisWorkbook however is filled with cells out of which many invoke a function. This function should change cells in wbDataFile.Sheets(1). Now, if I just try to read data from wbDataFile it works flawless, e.g.

Public Function x (...)
With wbDataFile.Sheets(1)
MsgBox "Test: " & .Cells(1,1).Value
End With
End Function

Runs totally fine. However, as soon as I try to manipulate data, the function does stop executing suddenly without any error message.

Public Function x (...)
With wbDataFile.Sheets(1)
.Cells(1,1).Value = "Teststring"
MsgBox "Hello World"
End With
End Function

In other words: neither gets 1,1 filled with "Teststring" nor does a MessageBox appear. When I try debug-mode, the function simply terminates in the line trying to change Cell 1,1.
Other things I have tried is using Range instead of Cells and using a global Worksheet instead of a Workbook. Same result.

Any suggestions?

Thanks a lot in advance!

 

[p45cal]

Try using Sub instead of Function.
Functions are for returning values rather than changing things.

 

[LazyJay]

Thanks for the idea but this is not an option. I need the return functionality of Function (as I said they are invoked from cells within the workbook).

 

[p45cal]

You won't get a function in a cell changing anything anywhere except the value in that cell.
You want changes, use a sub. It won't even work if you try to call a sub from a function.

 

[LazyJay]

Oh, thanks for pointing that out to me. I wasn't aware of this limitation. Do you know of any workaround to this problem? I need something which can be invoked from within a cell (i.e. =StartSub(param1, param2) in a cell in a worksheet).

What I had before is writing the data to txt-files using the Input command. This is actually usable out of a Function but unfortunately limits my options in other ways.

 

[p45cal]

You could use event-triggered subs to call the subs you want; selection_change, before_double-click, worksheet_change, are asome examples, all of which 'know' which cells are involved. Alternatively you could place a command button(s) on the sheet and assign vba to them.

 

[LazyJay]

Hi p45cal, thanks again for the suggestion! I think I will try something like that with my next project.

Because the forum was down yesterday I had to come up with my own workaround. Since Calculation in my workbook was done exclusively by the push of a button anyways, I let the functions write any necessary changes into a globally declared array (with two "columns" - one for the location of the change and one for the desired data after the change). Directly after the Calculate method finished (i.e. after all cells had invoked my Function) I put a loop to go through the array and perform all changes previously recorded in them.

Works like a charm

Thanks a lot again for your time!