Using excel sheet as a function












2















I wonder how I can use an excel sheet as a function.



Let's say I have a complex calculation in Sheet1 with one parameter I would like to iterate over a range like [1 to 400] in a second sheet to create an x, f(x) table in Sheet2.



I'll be using Excel or Open Office.



How would I go about doing this?



Thanks.





Progress update



Attempted to do a test in the Sheet (General):



Public Function test() as Boolean
test = True
End Function


Then in a cell:



=test()


Resulting in #Name?



Also tried scouering online tutorials.





Partially solved:




  • Visibility of the function is achieved by putting the code in a module.

  • Insert -> module in VBA.




Progress:



Setting av value using



 Worksheets("Sheet1").Range("A1").Value = 10

or

Worksheets("Sheet1").Cells(1,1).Value = 10


failes silently and doesn't execute the line under.





Progress:



The techniques work fine if I dumb down the code to a parameterless subroutine and assign it to a button.



Workaround



Implementation was really straight forward when implementing using a VBA macro launched from a button rather than using a function.



I iterated over a range of rows to get the inputs to the calculations.










share|improve this question




















  • 1





    RE the test function failing - have you enabled macros for the file in question? IIRC, with them disabled, custom functions will not operate.

    – DMA57361
    Sep 7 '10 at 13:04











  • Thanks, I think maybe it was a visibility issue. Moving the code to a module solved it. Now on to the hard part :)

    – tovare
    Sep 7 '10 at 13:06








  • 1





    Both those examples in your last update work for me (Excel 2003, what version are you using?). You can also use Sheets("Sheet1") and .Formula = 10 - both effectively the same when setting values (but Formula is quite different when retrieving). I doubt this will help though...

    – DMA57361
    Sep 7 '10 at 14:33











  • I tried two installations of Excel 2003. One had some VB-buttons disabled by the corporate admin, but I got around it. The second should be pretty unconstrained by coorporate snake oil, still didn't work though. I'll try some more tomorrow :-)

    – tovare
    Sep 7 '10 at 19:16






  • 1





    If the sheet is protected then you can't set the value. You either have to unprotect it, or user the USERINTERFACEONLY parameter when locking it.

    – Lance Roberts
    Sep 7 '10 at 20:28
















2















I wonder how I can use an excel sheet as a function.



Let's say I have a complex calculation in Sheet1 with one parameter I would like to iterate over a range like [1 to 400] in a second sheet to create an x, f(x) table in Sheet2.



I'll be using Excel or Open Office.



How would I go about doing this?



Thanks.





Progress update



Attempted to do a test in the Sheet (General):



Public Function test() as Boolean
test = True
End Function


Then in a cell:



=test()


Resulting in #Name?



Also tried scouering online tutorials.





Partially solved:




  • Visibility of the function is achieved by putting the code in a module.

  • Insert -> module in VBA.




Progress:



Setting av value using



 Worksheets("Sheet1").Range("A1").Value = 10

or

Worksheets("Sheet1").Cells(1,1).Value = 10


failes silently and doesn't execute the line under.





Progress:



The techniques work fine if I dumb down the code to a parameterless subroutine and assign it to a button.



Workaround



Implementation was really straight forward when implementing using a VBA macro launched from a button rather than using a function.



I iterated over a range of rows to get the inputs to the calculations.










share|improve this question




















  • 1





    RE the test function failing - have you enabled macros for the file in question? IIRC, with them disabled, custom functions will not operate.

    – DMA57361
    Sep 7 '10 at 13:04











  • Thanks, I think maybe it was a visibility issue. Moving the code to a module solved it. Now on to the hard part :)

    – tovare
    Sep 7 '10 at 13:06








  • 1





    Both those examples in your last update work for me (Excel 2003, what version are you using?). You can also use Sheets("Sheet1") and .Formula = 10 - both effectively the same when setting values (but Formula is quite different when retrieving). I doubt this will help though...

    – DMA57361
    Sep 7 '10 at 14:33











  • I tried two installations of Excel 2003. One had some VB-buttons disabled by the corporate admin, but I got around it. The second should be pretty unconstrained by coorporate snake oil, still didn't work though. I'll try some more tomorrow :-)

    – tovare
    Sep 7 '10 at 19:16






  • 1





    If the sheet is protected then you can't set the value. You either have to unprotect it, or user the USERINTERFACEONLY parameter when locking it.

    – Lance Roberts
    Sep 7 '10 at 20:28














2












2








2








I wonder how I can use an excel sheet as a function.



Let's say I have a complex calculation in Sheet1 with one parameter I would like to iterate over a range like [1 to 400] in a second sheet to create an x, f(x) table in Sheet2.



I'll be using Excel or Open Office.



How would I go about doing this?



Thanks.





Progress update



Attempted to do a test in the Sheet (General):



Public Function test() as Boolean
test = True
End Function


Then in a cell:



=test()


Resulting in #Name?



Also tried scouering online tutorials.





Partially solved:




  • Visibility of the function is achieved by putting the code in a module.

  • Insert -> module in VBA.




Progress:



Setting av value using



 Worksheets("Sheet1").Range("A1").Value = 10

or

Worksheets("Sheet1").Cells(1,1).Value = 10


failes silently and doesn't execute the line under.





Progress:



The techniques work fine if I dumb down the code to a parameterless subroutine and assign it to a button.



Workaround



Implementation was really straight forward when implementing using a VBA macro launched from a button rather than using a function.



I iterated over a range of rows to get the inputs to the calculations.










share|improve this question
















I wonder how I can use an excel sheet as a function.



Let's say I have a complex calculation in Sheet1 with one parameter I would like to iterate over a range like [1 to 400] in a second sheet to create an x, f(x) table in Sheet2.



I'll be using Excel or Open Office.



How would I go about doing this?



Thanks.





Progress update



Attempted to do a test in the Sheet (General):



Public Function test() as Boolean
test = True
End Function


Then in a cell:



=test()


Resulting in #Name?



Also tried scouering online tutorials.





Partially solved:




  • Visibility of the function is achieved by putting the code in a module.

  • Insert -> module in VBA.




Progress:



Setting av value using



 Worksheets("Sheet1").Range("A1").Value = 10

or

Worksheets("Sheet1").Cells(1,1).Value = 10


failes silently and doesn't execute the line under.





Progress:



The techniques work fine if I dumb down the code to a parameterless subroutine and assign it to a button.



Workaround



Implementation was really straight forward when implementing using a VBA macro launched from a button rather than using a function.



I iterated over a range of rows to get the inputs to the calculations.







microsoft-excel microsoft-excel-2003






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Sep 8 '10 at 10:39







tovare

















asked Sep 6 '10 at 19:58









tovaretovare

5281310




5281310








  • 1





    RE the test function failing - have you enabled macros for the file in question? IIRC, with them disabled, custom functions will not operate.

    – DMA57361
    Sep 7 '10 at 13:04











  • Thanks, I think maybe it was a visibility issue. Moving the code to a module solved it. Now on to the hard part :)

    – tovare
    Sep 7 '10 at 13:06








  • 1





    Both those examples in your last update work for me (Excel 2003, what version are you using?). You can also use Sheets("Sheet1") and .Formula = 10 - both effectively the same when setting values (but Formula is quite different when retrieving). I doubt this will help though...

    – DMA57361
    Sep 7 '10 at 14:33











  • I tried two installations of Excel 2003. One had some VB-buttons disabled by the corporate admin, but I got around it. The second should be pretty unconstrained by coorporate snake oil, still didn't work though. I'll try some more tomorrow :-)

    – tovare
    Sep 7 '10 at 19:16






  • 1





    If the sheet is protected then you can't set the value. You either have to unprotect it, or user the USERINTERFACEONLY parameter when locking it.

    – Lance Roberts
    Sep 7 '10 at 20:28














  • 1





    RE the test function failing - have you enabled macros for the file in question? IIRC, with them disabled, custom functions will not operate.

    – DMA57361
    Sep 7 '10 at 13:04











  • Thanks, I think maybe it was a visibility issue. Moving the code to a module solved it. Now on to the hard part :)

    – tovare
    Sep 7 '10 at 13:06








  • 1





    Both those examples in your last update work for me (Excel 2003, what version are you using?). You can also use Sheets("Sheet1") and .Formula = 10 - both effectively the same when setting values (but Formula is quite different when retrieving). I doubt this will help though...

    – DMA57361
    Sep 7 '10 at 14:33











  • I tried two installations of Excel 2003. One had some VB-buttons disabled by the corporate admin, but I got around it. The second should be pretty unconstrained by coorporate snake oil, still didn't work though. I'll try some more tomorrow :-)

    – tovare
    Sep 7 '10 at 19:16






  • 1





    If the sheet is protected then you can't set the value. You either have to unprotect it, or user the USERINTERFACEONLY parameter when locking it.

    – Lance Roberts
    Sep 7 '10 at 20:28








1




1





RE the test function failing - have you enabled macros for the file in question? IIRC, with them disabled, custom functions will not operate.

– DMA57361
Sep 7 '10 at 13:04





RE the test function failing - have you enabled macros for the file in question? IIRC, with them disabled, custom functions will not operate.

– DMA57361
Sep 7 '10 at 13:04













Thanks, I think maybe it was a visibility issue. Moving the code to a module solved it. Now on to the hard part :)

– tovare
Sep 7 '10 at 13:06







Thanks, I think maybe it was a visibility issue. Moving the code to a module solved it. Now on to the hard part :)

– tovare
Sep 7 '10 at 13:06






1




1





Both those examples in your last update work for me (Excel 2003, what version are you using?). You can also use Sheets("Sheet1") and .Formula = 10 - both effectively the same when setting values (but Formula is quite different when retrieving). I doubt this will help though...

– DMA57361
Sep 7 '10 at 14:33





Both those examples in your last update work for me (Excel 2003, what version are you using?). You can also use Sheets("Sheet1") and .Formula = 10 - both effectively the same when setting values (but Formula is quite different when retrieving). I doubt this will help though...

– DMA57361
Sep 7 '10 at 14:33













I tried two installations of Excel 2003. One had some VB-buttons disabled by the corporate admin, but I got around it. The second should be pretty unconstrained by coorporate snake oil, still didn't work though. I'll try some more tomorrow :-)

– tovare
Sep 7 '10 at 19:16





I tried two installations of Excel 2003. One had some VB-buttons disabled by the corporate admin, but I got around it. The second should be pretty unconstrained by coorporate snake oil, still didn't work though. I'll try some more tomorrow :-)

– tovare
Sep 7 '10 at 19:16




1




1





If the sheet is protected then you can't set the value. You either have to unprotect it, or user the USERINTERFACEONLY parameter when locking it.

– Lance Roberts
Sep 7 '10 at 20:28





If the sheet is protected then you can't set the value. You either have to unprotect it, or user the USERINTERFACEONLY parameter when locking it.

– Lance Roberts
Sep 7 '10 at 20:28










3 Answers
3






active

oldest

votes


















3














The best way is to create the algorithm in Excel VBA. You can open up VBA and type this



Public Function Test1(x as Integer, y as Integer)  
Test1 = x*y
End Function


You can call this function like any other function from the formula bar

In formula bar for A1





  • =Test1(2,4) with a result of 8.


Change the algorithm in VBA to get the results you are looking for.






share|improve this answer


























  • I'll try that, I just assumed it wouldn't work due to the amount of calculations that would need to be re-calculated in the first sheet every time.

    – tovare
    Sep 6 '10 at 20:22



















1














I was looking for an answer to a similar question and found something like that:



public function GetWhatever(byval p1 as variant, byval p2 as variant) as variant
'Make sure you're in AutoCalculation mode, otherwise use me.calculate
me.range("A1").value = p1
me.range("A2").value = p2
GetWhatever = me.range("A3").value
end function


SO question: https://stackoverflow.com/a/3570907






share|improve this answer

































    0














    One thing what I prefer is to create a DATA TABLE by varying the parameter and then LOOKUP for required value in the created data table.



    A more proper way will be to write a code in Excel VBA and it can be called very easily, without much effort. However, if you already have the Excel sheet in your hand, you can easily go with former one by creating a DATA TABLE.






    share|improve this answer























      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "3"
      };
      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


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f185382%2fusing-excel-sheet-as-a-function%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      3














      The best way is to create the algorithm in Excel VBA. You can open up VBA and type this



      Public Function Test1(x as Integer, y as Integer)  
      Test1 = x*y
      End Function


      You can call this function like any other function from the formula bar

      In formula bar for A1





      • =Test1(2,4) with a result of 8.


      Change the algorithm in VBA to get the results you are looking for.






      share|improve this answer


























      • I'll try that, I just assumed it wouldn't work due to the amount of calculations that would need to be re-calculated in the first sheet every time.

        – tovare
        Sep 6 '10 at 20:22
















      3














      The best way is to create the algorithm in Excel VBA. You can open up VBA and type this



      Public Function Test1(x as Integer, y as Integer)  
      Test1 = x*y
      End Function


      You can call this function like any other function from the formula bar

      In formula bar for A1





      • =Test1(2,4) with a result of 8.


      Change the algorithm in VBA to get the results you are looking for.






      share|improve this answer


























      • I'll try that, I just assumed it wouldn't work due to the amount of calculations that would need to be re-calculated in the first sheet every time.

        – tovare
        Sep 6 '10 at 20:22














      3












      3








      3







      The best way is to create the algorithm in Excel VBA. You can open up VBA and type this



      Public Function Test1(x as Integer, y as Integer)  
      Test1 = x*y
      End Function


      You can call this function like any other function from the formula bar

      In formula bar for A1





      • =Test1(2,4) with a result of 8.


      Change the algorithm in VBA to get the results you are looking for.






      share|improve this answer















      The best way is to create the algorithm in Excel VBA. You can open up VBA and type this



      Public Function Test1(x as Integer, y as Integer)  
      Test1 = x*y
      End Function


      You can call this function like any other function from the formula bar

      In formula bar for A1





      • =Test1(2,4) with a result of 8.


      Change the algorithm in VBA to get the results you are looking for.







      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited Sep 7 '10 at 13:24









      DMA57361

      16.9k66195




      16.9k66195










      answered Sep 6 '10 at 20:12









      wbeard52wbeard52

      2,74622238




      2,74622238













      • I'll try that, I just assumed it wouldn't work due to the amount of calculations that would need to be re-calculated in the first sheet every time.

        – tovare
        Sep 6 '10 at 20:22



















      • I'll try that, I just assumed it wouldn't work due to the amount of calculations that would need to be re-calculated in the first sheet every time.

        – tovare
        Sep 6 '10 at 20:22

















      I'll try that, I just assumed it wouldn't work due to the amount of calculations that would need to be re-calculated in the first sheet every time.

      – tovare
      Sep 6 '10 at 20:22





      I'll try that, I just assumed it wouldn't work due to the amount of calculations that would need to be re-calculated in the first sheet every time.

      – tovare
      Sep 6 '10 at 20:22













      1














      I was looking for an answer to a similar question and found something like that:



      public function GetWhatever(byval p1 as variant, byval p2 as variant) as variant
      'Make sure you're in AutoCalculation mode, otherwise use me.calculate
      me.range("A1").value = p1
      me.range("A2").value = p2
      GetWhatever = me.range("A3").value
      end function


      SO question: https://stackoverflow.com/a/3570907






      share|improve this answer






























        1














        I was looking for an answer to a similar question and found something like that:



        public function GetWhatever(byval p1 as variant, byval p2 as variant) as variant
        'Make sure you're in AutoCalculation mode, otherwise use me.calculate
        me.range("A1").value = p1
        me.range("A2").value = p2
        GetWhatever = me.range("A3").value
        end function


        SO question: https://stackoverflow.com/a/3570907






        share|improve this answer




























          1












          1








          1







          I was looking for an answer to a similar question and found something like that:



          public function GetWhatever(byval p1 as variant, byval p2 as variant) as variant
          'Make sure you're in AutoCalculation mode, otherwise use me.calculate
          me.range("A1").value = p1
          me.range("A2").value = p2
          GetWhatever = me.range("A3").value
          end function


          SO question: https://stackoverflow.com/a/3570907






          share|improve this answer















          I was looking for an answer to a similar question and found something like that:



          public function GetWhatever(byval p1 as variant, byval p2 as variant) as variant
          'Make sure you're in AutoCalculation mode, otherwise use me.calculate
          me.range("A1").value = p1
          me.range("A2").value = p2
          GetWhatever = me.range("A3").value
          end function


          SO question: https://stackoverflow.com/a/3570907







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited May 23 '17 at 12:41









          Community

          1




          1










          answered Feb 5 '14 at 13:33









          AsTeRAsTeR

          11413




          11413























              0














              One thing what I prefer is to create a DATA TABLE by varying the parameter and then LOOKUP for required value in the created data table.



              A more proper way will be to write a code in Excel VBA and it can be called very easily, without much effort. However, if you already have the Excel sheet in your hand, you can easily go with former one by creating a DATA TABLE.






              share|improve this answer




























                0














                One thing what I prefer is to create a DATA TABLE by varying the parameter and then LOOKUP for required value in the created data table.



                A more proper way will be to write a code in Excel VBA and it can be called very easily, without much effort. However, if you already have the Excel sheet in your hand, you can easily go with former one by creating a DATA TABLE.






                share|improve this answer


























                  0












                  0








                  0







                  One thing what I prefer is to create a DATA TABLE by varying the parameter and then LOOKUP for required value in the created data table.



                  A more proper way will be to write a code in Excel VBA and it can be called very easily, without much effort. However, if you already have the Excel sheet in your hand, you can easily go with former one by creating a DATA TABLE.






                  share|improve this answer













                  One thing what I prefer is to create a DATA TABLE by varying the parameter and then LOOKUP for required value in the created data table.



                  A more proper way will be to write a code in Excel VBA and it can be called very easily, without much effort. However, if you already have the Excel sheet in your hand, you can easily go with former one by creating a DATA TABLE.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Feb 20 at 4:35









                  Ankit TiwariAnkit Tiwari

                  1




                  1






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Super User!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f185382%2fusing-excel-sheet-as-a-function%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Probability when a professor distributes a quiz and homework assignment to a class of n students.

                      Aardman Animations

                      Are they similar matrix