1

Is it possible to execute code written in specific excel cells?

For example: In Range("A1") I have text -> debug.print("Test"). I need some way to execute this from VBA code.

Is it possible?

Community
  • 1
  • 1
Drac0
  • 101
  • 1
  • 2
  • 12

4 Answers4

4

First, use this answer to ensure programmatic access to the VBA model. Then, use this method that makes a string parser for a simple command located in a worksheet cell. This should be enough to get you going.

Demo:

enter image description here

Option Explicit

Sub test()
    Dim strCode As String
    strCode = Sheet1.Range("A1").Text
    StringExecute strCode
End Sub

Sub StringExecute(s As String) 
    ' Credits to A.S.H., see https://stackoverflow.com/questions/43216390/how-to-run-a-string-as-a-command-in-vba
    Dim vbComp As Object
    Set vbComp = ThisWorkbook.VBProject.VBComponents.Add(1)
    vbComp.CodeModule.AddFromString "Sub foo()" & vbCrLf & s & vbCrLf & "End Sub"
    Application.Run vbComp.Name & ".foo"
    ThisWorkbook.VBProject.VBComponents.Remove vbComp
End Sub
Ioannis
  • 5,238
  • 2
  • 19
  • 31
  • Can `Application.Run` line be replaced with `Evaluate foo`? – AntiDrondert Sep 06 '17 at 10:22
  • I do not think so: `Evaluate` parses a string to an excel-based formula, which is not the same as what `Application.Run` does. – Ioannis Sep 06 '17 at 10:26
  • `Worksheet("Sheet").Evaluate "0+Test()"` works for me though. I'm curious, are you programmatically creating separate module with `Sub` which consists of one line from cell? Kinda new to VBA, so anything new amazes me. – AntiDrondert Sep 06 '17 at 10:32
  • Does `Worksheet("Sheet").Evaluate "0+test()"` work as a replacement of `Application.Run`? It works as a way to call the `test` subroutine, but when I try to use it as a replacement for `Application.Run`, it fails. Yes, `StringExecute` takes a string which is the main body of a `Sub` and turns it into an actual `Sub`, it executes it and then it removes it. I have seen this in a couple of applications that connect to VBA/Excel, but needs to be quite defensive when programming like this in an actual application. – Ioannis Sep 06 '17 at 10:45
  • 1
    Hm, I was wrong, it can't evaluate non-existing `Sub`, though there is a workaround by adding another `Sub`/`Function` to `Call foo`. Anyway `Application.Run` is much easier. Thanks for tips. – AntiDrondert Sep 06 '17 at 10:55
0

No. You can't store code in the worksheet. But you can store the name of a procedure in a worksheet cell and then call that procedure using Application.Run

Variatus
  • 14,293
  • 2
  • 14
  • 30
0

Short answer: No

You can only run code that is within the VBA editor. Therefore it is not possible to run code from a cell directly.

But …
But what you can do (as a workaround) is writing a procedure that extracts the VBA code from that cell and includes it into a module and then runs that code.

However even if this is possible this would be a task for a pro-user and it is not recommended for beginners (you should know what you do here).

Nevertheless if you want to have a look into how to add procedures/functions into the VBA Editor have a look at Programming The VBA Editor.

Community
  • 1
  • 1
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • I just need to use simple functions like instr, abs and so on so i guess it would be easier to just look for specific words in cells and if they are found use specific funtions. – Drac0 Sep 06 '17 at 09:47
0

The closest you can easily get to your goal is to use VBA User Defined Functions. These can be called from worksheet formulas in the same way as native Excel functions.

But they have limitations compared to a VBA subroutine - the main one being that they can only return information to the cells that they occupy.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38