Use ActiveCell.Offset(0, 3).Value in a Module to set/change the Value

Question

I am new to VBA. Why can't I use following line in a module? But the same line seems to work fine in a sub

ActiveCell.Offset(0, 3).Value = "test"

Function trimto25(ByVal r As String) As String
    Dim i As Long
    Dim arr As Variant
    Dim erg As Variant
    
    arr = Split(r, " ")
    erg = arr(0)
    
    For i = 1 To UBound(arr)
    If (Len(erg + " " + arr(i)) < 25) Then erg = erg + " " + arr(i) Else: 
    Exit For
    Next i
    
    trimto25 = erg
    ActiveCell.Offset(0, 3).Value = "test"    
End Function

ERROR-CODE in Excel Sheet: #Value!

This seems to work fine: (But why does it work in the sub and not the function)

Sub s()
    ActiveCell.Offset(0, 3).Value = "test"
End Sub

Thanks for the critics, i edited my question again ! i hope this is now more conform — NoErrorNoCry, Jan 22 '21 at 13:51
Does this answer your question? [Excel VBA: Answer gets “stuck”](https://stackoverflow.com/q/3622137/11683) — GSerg, Jan 22 '21 at 13:58
Yes, thanks. That link is from Microsoft and explains the limitations of functions in generell and helped me too: https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1 — NoErrorNoCry, Jan 22 '21 at 14:12

score 0 · Accepted Answer · answered Jan 22 '21 at 13:25

0

This cannot be done. User defined functions cannot change the state of the workbook/worksheet, cell, ecc. Use subroutine instead.

answered Jan 22 '21 at 13:25

pizzettix

411
2
9

I am so sorry for the missunderstanding , i use it in an modul not in the cell – NoErrorNoCry Jan 22 '21 at 13:36
i edited my question now for better understanding :) – NoErrorNoCry Jan 22 '21 at 13:40
"But why does it work in the sub and not the function?": because in excel you can't change the e state of the workbook/worksheet, cell with a function only with a subroutine/macro. The software simply does not allow you to do this. – pizzettix Jan 22 '21 at 14:08
Thank you very much, you recognized the problem in the first moment !! – NoErrorNoCry Jan 22 '21 at 14:41

score 0 · Answer 2 · answered Jan 22 '21 at 13:26

0

The Offset command is VBA code rather than a formula. It needs to be part of a Macro/module/code to actually do anything.

answered Jan 22 '21 at 13:26

John Townend

13
4

I am so sorry, i use it in an modul – NoErrorNoCry Jan 22 '21 at 13:35

Use ActiveCell.Offset(0, 3).Value in a Module to set/change the Value

2 Answers2