Debugging our own code is hard enough, debugging someone else's code remotely is virtually impossible. What we can do is provide you with some guidance ti help you debug your code.
Basically the same code behaves in two different ways when called from different clients with the same parameters. The most likely explanation is that the VBA call is not passing the parameter values which you think it is - perhaps there is a misnamed cell or a implicit datatype conversion - but the only way to know is build some tracing into your code. This means simply writing debug messages from inside code.
The regrettably common way of doing this is using DBMS_OUTPUT, AKA The Devil's Debugger. This writes to the screen, which rules it out for your scenario because you want to call the procedure from VBA. So you can either write to a file using UTL_FILE or a LOG_TABLE. Using a log table is probably more of a performance hit than writing to an OS file, but it is easier to work with (at least if you're happier with SQL than sed and grep).
A crude tracing implementation woudl look something like this. Using AUTONOMOUS_TRANSACTION is optional but it guarantees trace records in the event that the main procedure hurls an exception; it also allows you to monitor the output in real time.
create or replace procedure your_proc
(p1 in number
, p2 in varchar2
...
, p38 in date)
is
....
procedure write_log
(p_action in varchar2
, p_add_text in varchar2 := null)
is
pragma autonomous_transaction;
begin
insert into log_table
(id, ts, action, add_text)
values
(log_seqno.nextval, systimestamp, p_action, p_add_text);
commit;
end write_log ;
begin
write_log ('YOUR_PROC::START', user);
write_log ('YOUR_PROC::PARAM', 'P1='||to_char(p1));
write_log ('YOUR_PROC::PARAM', 'P2='||p2);
....
write_log ('YOUR_PROC::PARAM', 'P32='||to_char(p32));
...
-- main body of procedure happens here
...
write_log ('YOUR_PROC::END');
exception
when some_exception then
write_log ('YOUR_PROC::EXCEPTION='||some_exception);
raise;
end your_proc;
As well as recording the values of the paramters you will want to record stages in the program flow, especially noting loops and conditiona; switch (IF.. ELSE, CASE, etc).
Run the program twice, once from VBA, once from SQL*Plus. Then compare the two sets of output. Where they differ, that will be you answer.