sdllc / basic-excel-r-toolkit Goto Github PK
View Code? Open in Web Editor NEWHome Page: http://bert-toolkit.com
Home Page: http://bert-toolkit.com
Hello everyone,
i am having an issue, in loading my vba macros.
when loading my vba macros in vba interface, using application.run to call bert functions, my program works correctly, but when i create a button for end users to call the macros, nothing happen.
does anyone have this issue too?
Thanks in advance for any suggestion :)
I have tried to build the solution with VS2015, without success.
Are there any plan to upgrade the solution?
The last line in the following instantly kills Excel and BERT without any notice.
install.packages("rJava")
library("rJava")
.jinit()
This works the same on both my PCs (Windows 10 and 7 - both x64, MS Office 2010 x32, JRE x32)
Standalone x32 and x64 Rs with correct respective JRE do not crash.
Is there any problem in Bert Console about reading the data?
My function involves a data so I have to read this data.
The home directory of the BERT console is C:\Users\SONY PC\AppData\Roaming\BERT
Although there is a text file which name is "bloodpressure.txt" in the same place. BERT can not read the tex file . It gives the warning message: Error in file(file, "rt") : cannot open the connection
In addition: Warning message:
In file(file, "rt") : cannot open file 'bloodpressure.txt': No such file or directory
What is the problem of not reading tex file although this tex file exists?
Without administrator privilege on Windows 2007, message “unable to write in C:\config.msi. Log on as administrator” is flashed. With administrator privilege, BERT is installed properly.
I'm absolutely a newbie to R, so forgive if I'm asking a stupid question: is there a way to expose directly all functions from a package? For example: I'm trying to expose to Excel special functions from package GSL; I could manually write interface function in Functions.R for them, but they are literally hundreths.
I don't know R sufficiently to make this automatic/programmatic.
P.S.: in this particular case, I've found that in the package source there are individual files (e.g., "bessel.R"): functions are defined there, and they simply call the gsl dll. So I could just source these files in Functions.R, and probably this would expose bessel functions to Excel. But this seems a kind of hack, and probably cannot be always applid.
Thanks for your suggestion on the previous issue.
Here's another problem. I am returning the following data table to Excel.
Classes 'data.table' and 'data.frame': 548 obs. of 3 variables:
$ Date : Date, format: "2015-02-01" "2015-02-02" ...
$ V1: num 107 106 NaN NaN 107 ...
$ V2: num 103 103 NaN NaN 103 ...
- attr(*, ".internal.selfref")=<externalptr>
Please advise. These NaN or NA should not show any numeric value.
I do not know why instruction given at R console behave differently- especially user defined functions. Sometime they work smoothly sometime they trapped in endless loop with message Processing Error: Is Excel Busy?. How to break this loop? Whether function behaves in ad hock manner due to buffering problem?
Tried it, works very well and I like it. But I'd increase MAX_FUNCTION_COUNT, 100 is definitevely too small (I made it 2,000, changing accordingly the def file and the thunks in bert.cpp)
I have created a function in R to plot two probability density functions and I was wondering how I display the plot that is created with this function in Excel. Using the function (R.PlotDens(vars)) in Excel returns 0.
Thanks in advance
PlotDens <- function(alpha_A,beta_A, alpha_B,beta_B){
require(ggplot2)
x <- seq(0, 1, length = 100)
y1 <- dbeta(x,alpha_A-1,beta_A-1)
y2 <- dbeta(x,alpha_B-1,beta_B-1)
plot <- ggplot() + geom_area(aes(x,y1), colour="dodgerblue3", fill="lightskyblue1", alpha=0.7, size=1.3) +
geom_area(aes(x,y2),colour="brown2", fill="lightcoral", alpha=0.5, size=1.3) +
scale_x_continuous(name = "Conversion rate") +
scale_y_continuous(name = "Probability density") +
ggtitle("Probability density functions of versions A and B") +
theme_bw()
}
Hi, i am using Bert in french, and i am having some issue when loadind a vba code like application.run("R.bertfunction"), i have this message when opening Bert console :
"Warning: l'espace de nom 'BERTModule' n'est pas disponible et a �t� remplac�
par .GlobalEnv lors du traitement de l'objet 'EXCEL'"
does anyone knows why ?
Thanks in advance
Hi,
I am presenting a problem by inserting a CustomXMLPart, is successful only the first call to Add, but in the second the answer is "an error occurred processing the command. Excel is busy?" then excel(version 2013 64 bits) crashes.
xmlString <-
paste("<?xml version=\"1.0\" encoding=\"utf-8\" ?>" ,
"<employees xmlns=\"http://schemas.microsoft.com/vsto/samples2\">" ,
"<employee>" ,
"<name>Karina Leal</name>" ,
"<hireDate>1999-04-01</hireDate>" ,
"<title>Manager</title>" ,
"</employee>" ,
"</employees>", sep="")
EXCEL$Application$get_ActiveWorkbook()$get_CustomXMLParts()$Add(xmlString, missing)
<environment: 0x000000000adfd070>
attr(,"class")
[1] "_CustomXMLPart" "IDispatch"
xmlString =
paste("<?xml version=\"1.0\" encoding=\"utf-8\" ?>" ,
"<outputvars xmlns=\"http://schemas.microsoft.com/vsto/samples\">" ,
"<outputvar>" ,
"<name>Karina Leal</name>" ,
"<hireDate>1999-04-01</hireDate>" ,
"<title>Manager</title>" ,
"</outputvar>" ,
"</outputvars>", sep="")
EXCEL$Application$get_ActiveWorkbook()$get_CustomXMLParts()$Add(xmlString, missing)
(an error occurred processing the command. is excel busy?)
Any idea what could be happening?
Whether there are limitations on reading number of rows and columns from Excel in R through BERT? I tried to read 396 columns and 51314 rows through Get.Range function. BERT failed to read it. It flashed message
Error in BERT$.Excel(16386, list(ref)) : Excel call failed (32)
and failed to read data of any size from Excel. After re-opening BERT and Excel, I tried to read data in increasing number of rows. After reading data successfully at one level, I removed it from R and then tried to read larger number of rows. I could reach to 20000 rows after which it showed message
Error in BERT$.Excel(16386, list(ref)) : Excel call failed (32).
Strangely after showing this message, BERT denied to read smaller number of rows which it read successfully earlier. Whether it is problem in memory allocation by BERT or garbage (created through BERT) collection at R?
Computer Detail
Processor: Intel Core 2 Duo
Operating System: Windows 7 (32 bit)
MS Excel 2007
BERT v 1.36
RAM 2 GB
Hello,
I keep getting this weird startup error message on startup:
"Sourced file OK: ExcelFunctions.R
Sourced file OK: functions.R
Error in file(filename, "r", encoding = encoding) :
cannot open the connection
In addition: Warning message:
In file(filename, "r", encoding = encoding) :
cannot open file 'ExcelFunctions.R': No such file or directory
Error reading startup file @ Fri Apr 21 14:59:30 2017"
Of course, what's odd is the combination of "Sourced file OK: ExcelFunctions.R" & "cannot open file 'ExcelFunctions.R': No such file or directory"
I have tried to change the startup directory etc but with no result.
Any clues ?
thanks !
I was trying to read data with header (data frame type) from excel. Get.Range()
some time fails to read data and flashes message
Error in BERT$.Excel(16386, list(ref)) :
SET_VECTOR_ELT() can only be applied to a 'list', not a 'NULL'
Some time it read data in single array and show error (in valid dimension) at time of using range.to.data.frame()
for line rng[-1, ]
Hello,
I'd like to share just my thought about the interplay between Excel and R.
BERT does an outstanding job by adding R functions to Excel front-end, it's useful having these available on a spreadsheet for light functions returning single results.
For heavy tasks I think users would love resorting to subroutines as they've no dependency on formula recalculation and give better customization regarding output arrangement. I know BERT scripting capabilities would resolve, but I think it's cleaner from VBA
Tipically such VBA subroutinewould act like an "offline" function
In general for the project, it would be nice having a BERT utility helping "getting-out" smoothly results from functions (the top would be preserving also results names in case of lists).
If I missed some functionality alread in BERT please excuse me. In general I think this would enhance BERT a lot.
I'd like to hear your opinion and any insight on that (a simple example follows)
Many thanks, Paolo
R CODE
ff<-function(v){
list(m=mean(v),e="hello",d=v)
}
VBA CODE
Sub run_ff()
a1 = Range("B3:B6") ' Reads data (real numbers)
x = Application.Run("BERT.Call", "ff", a1) 'run ff, x is variant loosing list names (Top: preseving names)
' Writes results
Range("A1") = x(1, 1) ' ok it works, scalar number
Range("A2") = x(2, 1) ' ok it works, scalar string
'Range("A3:A6") = x(3, 1) 'this would work (but one needs to specify range size)
Call getRes(x(3, 1), Range("A3")) ' Top: having a BERT help-function reading arg and resizing destination
End Sub
Hello,
just a couple of suggestions for your fantastic work that IMO would be very useful.
a) One button on the Excel menu, when clicked an input box appears , then the current Excel selection is set into R environment with the name choosen in the input box
b) One button on the Excel menu, when clicked an input box appears , then the corresponding choosen R variable is copy&pasted to the current Excel cell (which would be the upper-left corner in case of vector/matrix/data.frame)
c) An excel function that paste an R result into the spreadsheet, something like
=Rpaste(R.rnorm(100)) or from R console Rpaste(rnorm(100),cell="A10") with cell set by default to the current selection
All these would be useful because one would need just to select the starting cell to get some long result, overcoming Excel vector-formulas. I know that a) b) are available on RExcel and Matlab-Excel toolbox, about c) no idea.
Many thanks for your attention!
Paolo
Hello,
There seems to be a bug when a code is executed from a file using the "execute selected code" tool. Basically, and this happens only in loops, when there are too many lines of code, the execution freezes. This does not seem to depend on the actual content of the code : I had a loop that was working perfectly fine and simply adding a line of comment was enough to freeze the execution. The code written in the R console does not get to the last bracket of the loop.
Regards
Hello;
I am following the vba two examples and so far what I have tried its not working. I am trying to copy a range of cell with values to R, perform some statistics in R and get the results back to the same spreadsheet. Is it possible to do it? if so, could you provide a minimal example or direct me to a place in the wiki where I can read about it? Thanks
Tried 3 very simple functions: mean, sd, and diff. All of which I coded identical to SUM in your code, mutatis mutandis. I used a vertical column in excel of prices. Mean worked with no issue but with both sd and diff, I got a #VALUE error.
When attempt the converse and read the data into the console first using get.range, again SUM and MEAN worked without issue, but sd and mean result in "Error: stack usage xxxxxx is too close to the limit".
I am unfamiliar with this error and do not see why it would register. When I try typeof(matrix) is confirms that the vars is in fact "double", and I even tried to convert the matrix into a dataframe and only considering the first column of the matrix to no avail. Any assistance would be appreciated.
Using Win7, Excel 2010 (32-bit), BERT 0.40
Hi,
First of all thank you for developing this great application! I was wondering if you could help me on an issue with R functions that use formulas as their arguments. I wanted to integrate rpart function into excel, unfortunately I can't get it to work using 'BERT.Call'. I believe there is an issue with the formula, which is takes the following form 'predict_var ~.'. I would be very grateful for any hints!
Thank you!
I am using BERT 1.24. I want to incorporate `merge' (or join) type function in Excel with help of BERT. It is not very friendly to do same task with help of lookup functions. For this, my program takes references of merging ranges like "lev1!A1;D13" and "lev2!A1:G62" written at any place in sheet and selecting the written place. It works nicely if I perform following task at BERT console directly
`lev1<-range.to.data.frame(Get.Range(Text.Ref(z[1])),header=TRUE)
lev2<-range.to.data.frame(Get.Range(Text.Ref(z[2])),header=TRUE)
lev1_2<-merge.data.frame(lev1,lev2, all=TRUE)
wb <- EXCEL$Application$get_ActiveWorkbook()
new.sheet <- wb$get_Sheets()$Add()
new.sheet$put_Name("R_MergeData")
Activate.Sheet("R_MergeData")
out<-paste0("R1C1",":","R", dim(lev1_2)[1],"C",dim(lev1_2)[2])
Set.Range(Text.Ref(out),lev1_2)`
Unfortunately it does not works if I combine these codes as function and run them at BERT console. My function is
excel_merge3<-function() { z<-Get.Selection() lev1<-range.to.data.frame(Get.Range(Text.Ref(z[1])),header=TRUE) lev2<-range.to.data.frame(Get.Range(Text.Ref(z[2])),header=TRUE) lev1_2<-merge.data.frame(lev1,lev2, all=TRUE) wb <- EXCEL$Application$get_ActiveWorkbook() new.sheet <- wb$get_Sheets()$Add() new.sheet$put_Name("R_MergeData") Activate.Sheet("R_MergeData") out<-paste0("R1C1",":","R", dim(lev1_2)[1],"C",dim(lev1_2)[2]) Set.Range(Text.Ref(out),lev1_2) }
It gives message Error in BERT$.Excel(32768 + 109, list(ref)) : Excel call failed (32). Probably problem occurs at Set.Range.... Program post data in sheet from which takes information for merging ranges.
Other problem is that output is placed as array. For relatively larger data sets (5000 row and 15 columns) excel tries to put all data (5000*15) into each cell. It creates problem in handling the merge data.
Sorry, in case this is not the right forum for my question !
I am wondering whether it is possible to write simple R-code in the spreadsheet - say something along the line of:
and then export it to Bert-console?
This type of procedure is, for example, quite commonly used by traders in investment banks when one needs to quickly get indicative pricing of a new product without having to wait for a new release of the official pricing library.
It is ok to have all function prefixed with "R.".
However, having all R functions grouped in one category can become rapidly impractical (too many).
It would be nice to have the capability to define more than one category to which assign R functions. Maybe just based on the file in which functions are defined (e.g., "Special Functions in R.R")
This is not an issue, more something which might be added to the documentation. The docs show clearly how to run from the console macros that interact with Excel, but what if you want to run a macro directly from Excel? As Excel (Alt+F8) understands vba only, a vba trampoline is needed.
This is my attempt:
Sub EXEC(fun As String, ParamArray args())
Select Case UBound(args) + 1
Case 0: Application.Run "BERT.Call", fun
Case 1: Application.Run "BERT.Call", fun, args(0)
Case 2: Application.Run "BERT.Call", fun, args(0), args(1)
Case 3: Application.Run "BERT.Call", fun, args(0), args(1), args(2)
Case 4: Application.Run "BERT.Call", fun, args(0), args(1), args(2), args(3)
Case 5: Application.Run "BERT.Call", fun, args(0), args(1), args(2), args(3), args(4)
End Select
End Sub
Sub trycall()
' Here Set.Cell is a minimal example of an full R macro, which can interact with Excel, including the spreadsheet. EXEC <macro name>, arguments
EXEC "Set.Cell", "a1", 10
End Sub
Hi there,
we are just checking out if BERT is suited to replace our old interface between R and Excel. We really love the idea of this plugin!
However we came along a small issue where BERT interferes with the local environment. Obviously we have a local R installation for our tasks and we do call the local R scripts form VBA by using shell()
or CreateObject("WScript.Shell")
.
If we do call the local R installation from VBA via shell, BERT somehow interferres with the environment, so our local R installation looses the library path to the userdocs-folder (in Windows, R creates a local plugin library in the user's documents folder). If BERT is deactivated or we call the R script from a command line not opened by VBA, everything works fine however. So there is some interference with the environment.
Just wanted to let you know about this, as I think that dual installation and local execution of R via VBA will be an established task for users interested in BERT - If you have any hints how to avoid this issue, please let me know.
Best
freiwerk
I need help for creating a R function which may transfer R dataFrame of varying dimensions (based on inputs) to Excel.
Hi there,
First of all I want to applaud this project. The tests I did seem to work remarkably smooth and solid! This really is a game changer for those excel users that are sick of working with VBA.
Anyway, I speak here as a heavy duty user of both Excel and R in an enterprise financial environment. The only thing that really stops BERT from lifting off in an strictly controlled environment is the lack of bundling of Excel files and R scripts. Sharing files that belong together and versioning are a strict necessity, however currently BERT creates a strict barrier between the location of R and Excel.
It seems BERT only allows a single R file to be loaded at startup that must service all the different excel tools that I have to make. To make matters worse, for each tool multiple versions that need to be clearly segregated and auditable. I think you get my point that having just a single source file in some not so obvious user directory makes it very hard to distribute the amazing things I could make with BERT.
I think the this problem can easily be resolved by allowing each excel file to have an unique flag (perhaps a string variable in the vba code of that excel file) that I can set that directs BERT to the correct R file for that particular excel file. In this way, I can keep my excel and R file bundled and organised. Moreover I could quickly zip the bundleand share it with my coworkers.
Is it possible that in the future BERT could facilitate such a feature? In this way, BERT could become the greatest extension for R and excel in a similar way that Xlwings is for Python and excel.
I would love to hear your thoughts.
EDIT:
Excuse me for my ignorance, it seems BERT can facilitate multiple R files already. The issue only seems to be limited to BERT always looking in the same folder. If it would be possible to tell BERT for each individual excel file where to look for scripts, my (and I assume many other's) problem would be solved!
The UI is now stable enough to support internationalization / localization, and the code now (mostly) will support it. We just need volunteers to translate it into other languages. If you have any interest in helping out, please let me know -- either respond here or send me a direct mail.
Old issue but still exists in BERT 1.08
ret11NANANA <- function() {return(append(rep(1,2),rep(NA, 3)))}
In Excel I get 1 1 2,6965E+308 2,6965E+308 2,6965E+308
Hi there,
we du have some stability issues with BERT, it sometimes causes a problem at startup of Excel (addin: bertribbon.connect). Is there any log-file we can analyze?
Also, as the name indicates, only the ribbon is affected. Could we avoid such ribbon issues by simply loading BERTMGRXX.xll? Am I right that the COM addin is not needed?
Hi Duncan,
I have tried using Foo <- function( rng ){
process(rng);
}
to import forecast R functions into Excel (from the forecast package). The functions appear in Excel but when I try to use I get #Value!. Excel seems to read the code correctly, there are no errors when reading the start-up file, I am not sure what I am missing. I am particularly interested in the forecast.ets function.
Thank you,
Despina
I'm a bit of newbee around this, so please excuse my ignorance.
Excel isn't see BERT. I'm running at MacBook pro, using windows 10 through Bootcamp. I've installed BERT twice and it simply doesn't show up. It's unclear to me what's going on.
If this is a useful bit of information, Rexcel doesn't link to R either. There are several versions of R on the machine, ranging from simple R to Rstudio.
The warning message for R is that there's no R registered, while BERT simply isn't there.
Thanks.
Larry
I'm trying to run the kmeans.xlsb example and Excel 2016 is throwing up an Runtime 1004 error on the following line:
Set rng = sel.Offset(1, 0).Resize(sel.Rows.Count - 1, 1)
It doesn't look right, but I'm not sure how to fix it.
For BERT 1.24, problem occurs with R1C1 style while it is working nicely for A1 style.
`new.sheet<-wb$get_Sheets()$Add()
range<-new.sheet$get_Range("A1:J62")
range<-new.sheet$get_Range("R1C1:R62C10")
Error in new.sheet$get_Range("R1C1:R62C10") :
COM Exception in Invoke (Range) 0x80020009: Exception occurred.
`
I am trying to run glms models from data in Excel. How can I do this when BERT does not support functional arguments. The documentation seems to mention a work around but its quite general and I can't seem to make sense of it. Is it possible to show a working example where you are able to run a GLM model (or LM model for that matter) via BERT? Thanks in advance.
Hello everybody!
I was struggling to make some model work in excel and after comparing with my co-workers who don't have a problem with reading rJava package in BERT, I found out that I seem to be using a different version of R (R.Version() returns platform "i386-w64-mingw32"... instead of platform "x86-64-w64-mingw32". I have both versions of Java installed). I have re-installed R and also tried to erase files from //R 3.3.3/bin/i386 in BERT folder (which resulted in being unable to run BERT at all) with no success.
I would greatly apreciate any ideas about how to set BERT to use the 64-bit version instead of 32 and why on my coworkers' machines it works without any problems (we all use 64-bit OS, and I have ).
Thanks, Matija
The Fira Code font is great for code editing because it uses ligatures for things like arrows and inequalities.
To use Fira Code in the BERT editor, including ligatures, (and after you've installed the font on your machine), open the file bs-settings.json
. This will be in the BERT folder.
Add these settings to the "editor" section:
"fontFamily": "Fira Code Regular",
"fontSize": "13px",
"fontLigatures": true,
Obviously adjust size as desired. You can also use one of the heavier (or lighter) weights.
I have installed version 0.41 and tested it for 4 computers with Excel 2013 (both 32 and 64 bit) and Excel 2010 (both 32 and 64 bit). I found that the r console didn't work at Excel 2010 but worked perfectly at Excel 2013.
Besides, I found that the VBA worked at both Excel 2010 and Excel 2013 such as
I feel BERT is very great. I prefer BERT much more than RExcel. This is very bad if I can't use r console at my office which use Excel 2010. Is there any help for this? Thanks.
I'd like to know how to use bert without losing the excel pipeline capability.
Trying to find a simple way to open another Excel file from BERT console. shell.exec() does not work -no error messages are posted but the file doesn't open. (The same command works fine from R Studio.) Is there another way to accomplish this?
Passing a data frame to COM (e.g. range$put_Value
) only inserts the first column.
I had to reinstall BERT as some sort of conflict was happening between Excel Addons, but now something seems amiss. BERTMGR32.xll is no longer being installed into the Roaming\BERT directory, so Excel is having fits about it when I fire it up. I ran the msiexec with logging and while I see:
RegXLL: setting OPEN entry: "C:\Users\DrMCC\AppData\Roaming\BERT\BERTMGR32.xll"
I never see any install entry like this:
InstallFiles: File: BERT32.xll, Directory: C:\Users\DrMCC\AppData\Roaming\BERT, Size: 667648
So BERTMGR32.xll doesn't seem to be copied into the folder. Can anyone verify this is the case? I just tried it with a fresh download. Same issue. I'd built the project with my Visual Studio for kicks, but that can't be a reason for the failure, can it?
Matt
Hi Duncan,
This is very great for new version 0.55 comes with COM interface. I tried below commands are success:
EXCEL$Application$get_ActiveSheet()$get_UsedRange()$get_Rows()$get_Count()
EXCEL$Application$get_ActiveWorkbook()$get_Path()
However, when I tried some more complicated command which returns error. I'm not sure what's going wrong. Is it too complicated for BERT? I'm just interested what level I can use BERT to automate Excel task. BERT is very useful. I'm very appreciated you develop such tool.
EXCEL$Application$get_ThisWorkbook()
=> Error in EXCEL$Application$get_ThisWorkbook() :
COM Exception in Invoke (ThisWorkbook) 0x80020009: Exception occurred.
EXCEL$Application$get_Range("A1")$get_offset(0,1)$get_Value()
=> Error: attempt to apply non-function
Personally I find this logic sufficient:
This logic can be done in R (e.g., in Functions.R), but then it requires wrappers around all functions which must accept real AND complex values (e.g., sin). It would be nice to have two (input/output) R hook functions in do.call, initially set to identity, so that if necessary one can alter the default behaviour.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.