Very new to AS (and VBA for that matter). I am trying to pass VBA code from an AS application that I’ve written into Excel as a function, to then run an Excel macro to evaluate the result of the function.
Initially I thought applescript’s do Visual Basic would work, only to discover that command is unavailable in Excel. The next best thing seems to be the RecordMacro BasicCode command. I can’t get this to do anything though. Is there something I am missing? Do I need to specify a sheet?
tell application "Microsoft Excel"
Activate
RecordMacro BasicCode "sub hello()
Range("A1").Value = "hello"
end sub"
Evaluate "Workbook1!hello()" --checking the macro
end tell
This compiles and runs, but does nothing. The basic code pasted into the VBE works fine incidentally.
Any help or suggestions would be greatly appreciated.
RecordMacro is to record a single line of code while you are recording a macro (which seems that it can only be accomplished manually). So switch to your Excel sheet, hit record, enter the name “hello” then run this script:
I couldn’t figure out how to pass the single line of code as an executable (in Word, you can via: do Visual Basic “vba code” ) or how to tell Excel to start recording a macro programatically. If you just want to set the value of a cell, however, you’re better just using AppleScript alone (Excel has very robust AppleScript support) just use:
I appreciate the quick response. My problem is while this may work, the time required to do it (physically pressing ‘record’) may be better spent just pasting the code into the VB editor. I assume it may be possible to use UI scripting to activate the recording or bring up the VB editor to automatically paste my code, but I am wondering if just working within the cells may not just be a better solution.
What I am trying to accomplish is this:
I have many (100’s a day) variable length equations that I need to apply to 1000+ rows of time-series data. The result of this I then analyze via another pre-coded VBA routine.
Keeping this all in VBA would be ideal, but if its not possible I may be able to do the equation part in a unix statistics program and then pass the result to VBA.
Do you have any idea why do Visual Basic is unavailable in such a VBA enabled program?
I don’t understand what you are trying to do. If your VBA routines are static, why not just enter them as macros in Excel once and then run them as necessary? If you are looking to enter variable data into the cells of your sheet, just use the code I listed about with a repeat block looping through the values and incrementing the cells they are added to with an additional (optional) bit of code to select the current cell(s) and run your VBA macros via the “evaluate” command (which does work for pre-existing macros).
Sorry for not being very clear. What I am really trying to do is write a function in VBA. The function needs to change with each new model constructed. An abreviated part of the code looks like this:
Function ModelAUTO(D,Vo,F,V,O,I,C)
Dim z11 as Double, z21 as Double, z31 as Double, z41 as Double, z51 as Double
Dim z61 as Double, z71 as Double, z81 as Double, z91 as Double, z101 as Double
Dim z12 as Double, z22 as Double, z32 as Double, z42 as Double, z52 as Double
Dim z62 as Double, z72 as Double, z82 as Double, z92 as Double, z102 as Double
Dim X16 as Double
Dim X8 as Double...
z12 = (+ 3.183e-1 * X4) - (1.912e+1 * X8) - (8.446e+0)
z11 = (+ 9.071e+1 * X14) + (2.190e-1)
z22 = (+ 1.063e+0 * z12) - (1.173e+0 * z11 * z12)
z12 = (+ 2.817e-1 * X1) - (7.482e+0)
z11 = (+ 6.065e+1 * X12) + (1.493e+0)
z21 = (+ 6.790e-1 * z12) - (2.391e+0 * z11 * z12) + (5.551e+0 * z12 * z12)
z32 = (+ 6.931e-1 * z21) + (7.603e-1 * z22)
z11 = (+ 7.713e+1 * X13) + (7.384e-2)
z21 = (+ 1 * z11)
X16 = (+ 3.813e-4 * z62) - (1.815e-5)
End Function
This equation is part of one of many 100’s I need to evaluate that come from another program. The evaluation macro is static, but the unique functions need to be written each time a new one is generated. Each function is applied to a static data set, the result of which is then evaluated by the static evaluation macro.
The problem is how do I input this ‘function’ so I can get a result from the data set?
This is easy in VBA, but apparantly it is not to easy to automate the writing of functions/macros in Excel.