Geek Answers Handbook

VBA cannot write data to Excel 2007/2010 cells inside function

I want to set a value for cells using VBA. I have googled and see some resolution:

Sheets("SheetName").Range("A1").value = someValue
Sheets("SheetName").Cells(1,1).value = someValue

With this code, I can just read the data from cell A1, but I cannot set a new value for it.

Update

The code for setting the value of cell A1 is placed inside Function, as shown below.

Function abb()
    Sheets("SheetName").Range("A1").value = 122333
    abb = 'any thing'
End Function

In cell B2 I installed =abb()and press enter. I get #VALUE but nothing happens in A1.

By putting this code in a macro, it works.

My question is: how to make A1 have values ​​inside the function?

+5
source share
4 answers

From your comment above you wanted to try this approach

=abb()

A1 ​​ 12333

,
Range("A1").Value = 122333

, Excel ( UDF )

aka Zorvek, EE Paywall ( , )

Excel UDF , , , , UDF Windows Application.OnTime . Windows UDF, Excel Application.OnTime UDF. Windows (Excel , Windows VBA, ), Application.OnTime , , Excel , , .

, Windows UDF, Application.OnTime, , UDF , . .

Declare Function SetTimer Lib "user32" ( _
      ByVal HWnd As Long, _
      ByVal nIDEvent As Long, _
      ByVal uElapse As Long, _
      ByVal lpTimerFunc As Long _
   ) As Long

Private Declare Function KillTimer Lib "user32" ( _
      ByVal HWnd As Long, _
      ByVal nIDEvent As Long _
   ) As Long

Private mCalculatedCells As Collection
Private mWindowsTimerID As Long
Private mApplicationTimerTime As Date

Public Function abb()

' This is a UDF that returns the sum of two numbers and starts a windows timer
' that starts a second Appliction.OnTime timer that performs activities not
' allowed in a UDF. Do not make this UDF volatile, pass any volatile functions
' to it, or pass any cells containing volatile formulas/functions or
' uncontrolled looping will start.

   abb = "Whatever you want"

   ' Cache the caller reference so it can be dealt with in a non-UDF routine
   If mCalculatedCells Is Nothing Then Set mCalculatedCells = New Collection
   On Error Resume Next
   mCalculatedCells.Add Application.Caller, Application.Caller.Address
   On Error GoTo 0

   ' Setting/resetting the timer should be the last action taken in the UDF
   If mWindowsTimerID <> 0 Then KillTimer 0&, mWindowsTimerID
   mWindowsTimerID = SetTimer(0&, 0&, 1, AddressOf AfterUDFRoutine1)

End Function

Public Sub AfterUDFRoutine1()

' This is the first of two timer routines. This one is called by the Windows
' timer. Since a Windows timer cannot run code if a cell is being edited or a
' dialog is open this routine schedules a second safe timer using
' Application.OnTime which is ignored in a UDF.

   ' Stop the Windows timer
   On Error Resume Next
   KillTimer 0&, mWindowsTimerID
   On Error GoTo 0
   mWindowsTimerID = 0

   ' Cancel any previous OnTime timers
   If mApplicationTimerTime <> 0 Then
      On Error Resume Next
      Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2", , False
      On Error GoTo 0
   End If

   ' Schedule timer
   mApplicationTimerTime = Now
   Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2"

End Sub

Public Sub AfterUDFRoutine2()

' This is the second of two timer routines. Because this timer routine is
' triggered by Application.OnTime it is safe, i.e., Excel will not allow the
' timer to fire unless the environment is safe (no open model dialogs or cell
' being edited).

   Dim Cell As Range

   ' Do tasks not allowed in a UDF...
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Do While mCalculatedCells.Count > 0
      Set Cell = mCalculatedCells(1)
      mCalculatedCells.Remove 1
      Range("A1").Value = 122333
   Loop
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   End Sub
+11

A1 B2.

: Excel. :

" , , Microsoft Excel. , :

  • , .
  • . [ ]
  • , , .
  • , .
  • .
  • .

A1 ? , , - .

+5

, . :

Function abb()
    Dim arr As Variant
    ReDim arr(1 To 2)
    arr(1) = "aardvark"
    arr(2) = "bee"
    abb = arr
End Function

A2 - B2. =abb() Shift Ctrl Enter, , . (A2 B2).

, , , .

+2

-

  • excel
  • Sheets("Sheet1").Range("A1").Value2 = "value"

.Value, .Value2, , .

0

More articles:


All Articles