Styjun

How come I was asked by a CBP officer why I was in the US?

What reason would an alien civilization have for building a Dyson Sphere (or Swarm) if cheap Nuclear fusion is available?

Is there a maximum distance from a planet that a moon can orbit?

Does Marvel have an equivalent of the Green Lantern?

Does ultrasonic bath cleaning damage laboratory volumetric glassware calibration?

What is the legal status of travelling with (unprescribed) methadone in your carry-on?

Why would people reject a god's purely beneficial blessing?

Going to get married soon, should I do it on Dec 31 or Jan 1?

Why is Madam Hooch not a professor?

Cascading Repair Costs following Blown Head Gasket on a 2004 Subaru Outback

Through the Looking-Glass

First-year PhD giving a talk among well-established researchers in the field

Short and long term plans in a closed game in the Sicilian Defense

Should I tell my insurance company I'm making payments on my new car?

How can I deal with a coworker killed on the job

How to get cool night-vision without lame drawbacks?

Why is the Turkish president's surname spelt in Russian as Эрдоган, with г?

C-152 carb heat on before landing in hot weather?

Did Karl Marx ever use any example that involved cotton and dollars to illustrate the way capital and surplus value were generated?

Analog is Obtuse!

Abel-Jacobi map on symmetric product of genus 4 curve

Is my Rep in Stack-Exchange Form?

Fedora boot screen shows both Fedora logo and Lenovo logo. Why and How?

Impossible darts scores

After Setting a value in cell function/sub then program exits - Macro VBA

Excel VBA Macros to copy and paste cell value to certain cells based on text in columnIs it possible to make VBA Macro function on the level of file?VBA user-defined function to copy value, text formation and hyperlink of a cell?Excel VBA - read cell value from codeHow to Call VBA Function from Excel Cells?Excel VBA: Cell Value from MacroExcel VBA not setting a cells value to a BSTR correctlyinterior.colorindex on VBA-autofilter hidden cell ends function with #VALUEVBA Function not returning a valueVBA Excel : Macro that searches a row for a certain variable value from specific cell and then copy&pastes value to this column

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

After searching more for this solution I asked in SO. I just trying to automate the work and new bie in VBA code. Here I m facing problem that when I update cell value in excel using macro. Then the program stops it not procceds to next step.

My sample code:

Private Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 Set Workx = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 MsgBox Workx
 trim_Func = "Yes"
End Function

Here After this line "ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr the function stopped not calling MsgBox or anyother. But the value is changed. I have another function after this to be called.

Edit: The Trim function is called like this after a button click in excel sheet.

Private Sub CommandButton1_Click()
Dim data_value As String
data_value = trim_Func(2, 6)

// Some other conditions and functions

Edit :

I have provided sample tested screenshot. It does not return function or not going next to that value changed in cell.
Excel:

Code:

excel vba automation

share|improve this question

edited Mar 25 at 11:10

User Learning

asked Mar 25 at 10:29

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How is the function trim_Func called? Can you please add this to your question (edit). Best is to provide a minimal reproducible example so everyone can reproduce what you did.
  
  – Pᴇʜ
  Mar 25 at 10:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, I have added when it is called in code. After a button click the function is called to trim the active cell.
  
  – User Learning
  Mar 25 at 10:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You do not need a function for this. Change it to Sub trim_Func(i As Integer, j As Integer)
  
  – Siddharth Rout
  Mar 25 at 10:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I cannot reproduce your issue. The code is running fine here. It returns yes as expected. What is the exact value in Cells(2, 6)?
  
  – Pᴇʜ
  Mar 25 at 10:48
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SiddharthRout as long as he wants to return "yes" he needs a function (even if it doesn't make sense as it is now). Not clear what he is doing with this "yes" but at least it got asigned to a variable. Well, I would recommend to use a Boolean instead of a String if it is just a yes/no return.
  
  – Pᴇʜ
  Mar 25 at 10:50
  
  

  
  
  
  

 | 
show 6 more comments

0

After searching more for this solution I asked in SO. I just trying to automate the work and new bie in VBA code. Here I m facing problem that when I update cell value in excel using macro. Then the program stops it not procceds to next step.

My sample code:

Private Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 Set Workx = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 MsgBox Workx
 trim_Func = "Yes"
End Function

Here After this line "ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr the function stopped not calling MsgBox or anyother. But the value is changed. I have another function after this to be called.

Edit: The Trim function is called like this after a button click in excel sheet.

Private Sub CommandButton1_Click()
Dim data_value As String
data_value = trim_Func(2, 6)

// Some other conditions and functions

Edit :

I have provided sample tested screenshot. It does not return function or not going next to that value changed in cell.
Excel:

Code:

excel vba automation

share|improve this question

edited Mar 25 at 11:10

User Learning

asked Mar 25 at 10:29

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How is the function trim_Func called? Can you please add this to your question (edit). Best is to provide a minimal reproducible example so everyone can reproduce what you did.
  
  – Pᴇʜ
  Mar 25 at 10:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, I have added when it is called in code. After a button click the function is called to trim the active cell.
  
  – User Learning
  Mar 25 at 10:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You do not need a function for this. Change it to Sub trim_Func(i As Integer, j As Integer)
  
  – Siddharth Rout
  Mar 25 at 10:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I cannot reproduce your issue. The code is running fine here. It returns yes as expected. What is the exact value in Cells(2, 6)?
  
  – Pᴇʜ
  Mar 25 at 10:48
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SiddharthRout as long as he wants to return "yes" he needs a function (even if it doesn't make sense as it is now). Not clear what he is doing with this "yes" but at least it got asigned to a variable. Well, I would recommend to use a Boolean instead of a String if it is just a yes/no return.
  
  – Pᴇʜ
  Mar 25 at 10:50
  
  

  
  
  
  

 | 
show 6 more comments

After searching more for this solution I asked in SO. I just trying to automate the work and new bie in VBA code. Here I m facing problem that when I update cell value in excel using macro. Then the program stops it not procceds to next step.

My sample code:

Private Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 Set Workx = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 MsgBox Workx
 trim_Func = "Yes"
End Function

Here After this line "ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr the function stopped not calling MsgBox or anyother. But the value is changed. I have another function after this to be called.

Edit: The Trim function is called like this after a button click in excel sheet.

Private Sub CommandButton1_Click()
Dim data_value As String
data_value = trim_Func(2, 6)

// Some other conditions and functions

Edit :

I have provided sample tested screenshot. It does not return function or not going next to that value changed in cell.
Excel:

Code:

excel vba automation

share|improve this question

edited Mar 25 at 11:10

User Learning

asked Mar 25 at 10:29

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

Private Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 Set Workx = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 MsgBox Workx
 trim_Func = "Yes"
End Function

Private Sub CommandButton1_Click()
Dim data_value As String
data_value = trim_Func(2, 6)

// Some other conditions and functions

share|improve this question

edited Mar 25 at 11:10

User Learning

asked Mar 25 at 10:29

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

share|improve this question

edited Mar 25 at 11:10

User Learning

asked Mar 25 at 10:29

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

asked Mar 25 at 10:29

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

asked Mar 25 at 10:29

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

1 Answer
1

active

oldest

votes

0

Thanks all. I have found solution after much thinking. Made some mistake by asigning value in Set. Thats problem.

Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
 ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 trim_Func = "Yes"
End Function

share|improve this answer

answered Mar 25 at 12:13

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I wouldn't advice to use ActiveWorkbook as at any given point, active workbook could not be the workbook you want to interact with. Qualifying the workbook would reduce unwanted issues
  
  – Zac
  Mar 25 at 13:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, So ThisWorkbook.Sheets is recommended ? I m newbie so thanks for suggestions. @Zac
  
  – User Learning
  Mar 25 at 14:03
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @UserLearning ActiveWorkbook is the workbook window that has the focus (is on top) which can easily change by a mouse click for example. While ThisWorkbook is always the workbook the current code is running in. So this is more reliable because it cannot be changed by user interaction.
  
  – Pᴇʜ
  Mar 25 at 14:06
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Pᴇʜ: perfect explanation! :)
  
  – Zac
  Mar 25 at 14:29
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55335743%2fafter-setting-a-value-in-cell-function-sub-then-program-exits-macro-vba%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Thanks all. I have found solution after much thinking. Made some mistake by asigning value in Set. Thats problem.

Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
 ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 trim_Func = "Yes"
End Function

share|improve this answer

answered Mar 25 at 12:13

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I wouldn't advice to use ActiveWorkbook as at any given point, active workbook could not be the workbook you want to interact with. Qualifying the workbook would reduce unwanted issues
  
  – Zac
  Mar 25 at 13:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, So ThisWorkbook.Sheets is recommended ? I m newbie so thanks for suggestions. @Zac
  
  – User Learning
  Mar 25 at 14:03
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @UserLearning ActiveWorkbook is the workbook window that has the focus (is on top) which can easily change by a mouse click for example. While ThisWorkbook is always the workbook the current code is running in. So this is more reliable because it cannot be changed by user interaction.
  
  – Pᴇʜ
  Mar 25 at 14:06
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Pᴇʜ: perfect explanation! :)
  
  – Zac
  Mar 25 at 14:29
  

  
  
  
  

add a comment | 

0

Thanks all. I have found solution after much thinking. Made some mistake by asigning value in Set. Thats problem.

Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
 ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 trim_Func = "Yes"
End Function

share|improve this answer

answered Mar 25 at 12:13

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I wouldn't advice to use ActiveWorkbook as at any given point, active workbook could not be the workbook you want to interact with. Qualifying the workbook would reduce unwanted issues
  
  – Zac
  Mar 25 at 13:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, So ThisWorkbook.Sheets is recommended ? I m newbie so thanks for suggestions. @Zac
  
  – User Learning
  Mar 25 at 14:03
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @UserLearning ActiveWorkbook is the workbook window that has the focus (is on top) which can easily change by a mouse click for example. While ThisWorkbook is always the workbook the current code is running in. So this is more reliable because it cannot be changed by user interaction.
  
  – Pᴇʜ
  Mar 25 at 14:06
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Pᴇʜ: perfect explanation! :)
  
  – Zac
  Mar 25 at 14:29
  

  
  
  
  

add a comment | 

Thanks all. I have found solution after much thinking. Made some mistake by asigning value in Set. Thats problem.

Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
 ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 trim_Func = "Yes"
End Function

share|improve this answer

answered Mar 25 at 12:13

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

Function trim_Func(i As Integer, j As Integer) As String
 Dim fStr As String
 fStr = ActiveWorkbook.Sheets("Sales").Cells(i, j)
 fStr = trim(fStr)
 ActiveWorkbook.Sheets("Sales").Cells(i, j) = fStr
 trim_Func = "Yes"
End Function

share|improve this answer

answered Mar 25 at 12:13

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

answered Mar 25 at 12:13

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges

answered Mar 25 at 12:13

User LearningUser Learning

1,68133 gold badges1919 silver badges4242 bronze badges