Automate a function in Excel using VBA

P

ppuvv2

New Member
Joined
May 11, 2018
Messages
1
Hi,

I am new to Excel VBA abd have been stuck in this problem for quite some time now. I am required to
read (n) number of test cases with (n) number of inputs and display the outputs through a button macro in developer ribbon. I'm looking to write a function to accomplish this and any help that I can get in order to complete the task would be greatly appreciated. I want a function so that my code runs for different tyes of inputs and stays generic. Thank you very much.
​

This is the excel file from which I'm taking the different inputs.


Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Test Casesinput_BirthDateinput_HireDateinput_Genderinput_AnnualPlanCompinput_EmployeeClassinput_AnnualPayGrowthinput_MarketPerformanceinput_PVDinput_NRAinput_MBAAinput_Custom_BCDinput_Custom_TermAgeinput_RemainingElectionsinput_Pre2011ServiceYearsinput_ProjectedServiceYearsinput_ProjectedBenefitAmountinput_DROP_LumpSuminput_ProjBuyBackABOinput_DateABOinput_ProjectedABOinput_ProjectedAALinput_DateAALinput_InvestmentBalanceTBAinput_CurrentABO
216/5/19548/1/2013F50224Regular School District1Average706265646414117028.5802/28/2018103461.5486963/31/2018080772.6
321/4/198310/1/2012F36179.33Regular School District1Average3863663590106.3304461.1801/31/20184461.1801/1/190003340.03

<colgroup><col style="px"width:" 25pxpx"=""><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
ip​



My code on Vba till now i worked on looks like this
Code: 
Sub xp()

    Dim http As Object
    Dim st As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    st =  "Site=SBA_Modeler_V22&Data={'input_BirthDate':'1983-01-04','input_HireDate':'2012-10-01','input_Gender':'F','input_AnnualPlanComp':36179.33,'input_EmployeeClass':'Regular  School  District','input_AnnualPayGrowth':1,'input_MarketPerformance':'Average','input_PVD':38,'input_NRA':63,'input_MBAA':66,'input_Custom_BCD':35,'input_Custom_TermAge':90,'input_RemainingElections':1,'input_Pre2011ServiceYears':0,'input_ProjectedServiceYears':6.33,'input_ProjectedBenefitAmount':0,'input_DROP_LumpSum':4461.18,'input_ProjBuyBackABO':0,'input_DateABO':2018-01-31,'input_ProjectedABO':4461.18,'input_ProjectedAAL':0,'input_DateAAL':1900-01-01,'input_InvestmentBalanceTBA':0,'input_CurrentABO':3340.03}"
   
 
    http.Open "POST", "https://beqlb02.poolt.hewitt.com/dsi0042/calculator", False
    http.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    http.send (st)

    MsgBox (http.responsetext)
    Set JSON = ParseJson("[" + http.responsetext + "]")
    i = 1
    For Each Item In JSON
    Sheets(4).Cells(3, 2).Value = JSON(1).Item("output_Range_Age1")
    Sheets(4).Cells(3, 3).Value = JSON(1).Item("output_Range_Age2")
    Sheets(4).Cells(3, 4).Value = JSON(1).Item("output_Range_Age3")
    Sheets(4).Cells(3, 5).Value = JSON(1).Item("output_Range_Age4")
    Sheets(4).Cells(3, 6).Value = JSON(1).Item("output_Range_Age5")
    Sheets(4).Cells(3, 7).Value = JSON(1).Item("output_Range_Custom")
    Sheets(4).Cells(3, 8).Value = JSON(1).Item("output_Balance_Age1")
    Sheets(4).Cells(3, 9).Value = JSON(1).Item("output_Balance_Age2")
    Sheets(4).Cells(3, 10).Value = JSON(1).Item("output_Balance_Age3")
    Sheets(4).Cells(3, 11).Value = JSON(1).Item("output_Balance_Age4")
    Sheets(4).Cells(3, 12).Value = JSON(1).Item("output_Balance_Age5")
    Sheets(4).Cells(3, 13).Value = JSON(1).Item("output_Balance_Custom")
    Sheets(4).Cells(3, 14).Value = JSON(1).Item("output_Balance_LumpSum")
    Sheets(4).Cells(3, 16).Value = JSON(1).Item("output_CurrentAge")
    Sheets(4).Cells(3, 17).Value = JSON(1).Item("output_MinAgeTerm")
    Sheets(4).Cells(3, 18).Value = JSON(1).Item("output_MaxAgeTerm")
    Sheets(4).Cells(3, 19).Value = JSON(1).Item("output_MinAgeBCD")
    Sheets(4).Cells(3, 20).Value = JSON(1).Item("output_MaxAgeBCD")
    Sheets(4).Cells(3, 22).Value = JSON(1).Item("output_DROP_Question")
    Sheets(4).Cells(3, 23).Value = JSON(1).Item("output_DROP_Years")
    Sheets(4).Cells(3, 24).Value = JSON(1).Item("output_DROP_Start")
    Sheets(4).Cells(3, 25).Value = JSON(1).Item("output_DROP_1stYear")
    Sheets(4).Cells(3, 26).Value = JSON(1).Item("output_DROP_Acumulation")
    Sheets(4).Cells(3, 27).Value = JSON(1).Item("output_DROP_AccumulationAnnuity")
    Sheets(4).Cells(3, 28).Value = JSON(1).Item("output_DROP_TotalAnnuity")
    Sheets(4).Cells(3, 29).Value = JSON(1).Item("output_DROP_COLA")
    Sheets(4).Cells(3, 31).Value = JSON(1).Item("output_BuyBack_Payment")
    Sheets(4).Cells(3, 32).Value = JSON(1).Item("output_BuyBack_IP_AddOn")
    Sheets(4).Cells(3, 33).Value = JSON(1).Item("output_DROP_BuyBack_TotalAnnuity")
    
    i = i + 1
    Next
    
    MsgBox ("complete")
 
End Sub


The following is the excel file from where I am deriving the inputs for different test cases.

Excel 2010
CDEFG
2input_BirthDate6/5/1954output_Range_Age170
3input_HireDate8/1/2013output_Range_Age2
4input_GenderFoutput_Range_Age3
5input_AnnualPlanComp50224output_Range_Age4
6input_EmployeeClassRegular School Districtoutput_Range_Age5
7input_AnnualPayGrowth1output_Range_Custom64
8input_MarketPerformanceAverageoutput_Balance_Age1 $ -
9output_Balance_Age2 $ -
10input_PVD70output_Balance_Age3 $ -
11input_NRA62output_Balance_Age4 $ -
12input_MBAA65output_Balance_Age5 $ -
13input_Custom_BCD64output_Balance_Custom $ -
14input_Custom_TermAge64output_Balance_LumpSum $ -
15
16input_RemainingElections1output_CurrentAge63
17output_MinAgeTerm64
18input_Pre2011ServiceYears4output_MaxAgeTerm80
19input_ProjectedServiceYears11output_MinAgeBCD70
20input_ProjectedBenefitAmount7028.58output_MaxAgeBCD65
21input_DROP_LumpSum0
22output_DROP_QuestionFALSE
23input_ProjBuyBackABOoutput_DROP_Years
24input_DateABO2/28/2018output_DROP_Start
25input_ProjectedABO103461.51output_DROP_1stYear
26input_ProjectedAAL48696output_DROP_Accumulation
27input_DateAAL3/31/2018output_DROP_AccumulationAnnuity
28input_InvestmentBalanceTBA0output_DROP_TotalAnnuity
29input_CurrentABO80772.6output_DROP_COLA1.09%
30
31output_BuyBack_PP $ 48,696.00
32output_BuyBack_Payment $ 48,696.00
33output_BuyBack_IP_AddOn $ 2,627.08
34output_BuyBack_TotalAnnuity $ 2,627.08

<colgroup><col style="px"width:" 25pxpx"=""><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Inputs Outputs​

Worksheet Formulas
CellFormula
G2=Model!B33
G3=Model!B34
G4=Model!B35
G5=Model!B36
G6=Model!B37
G7=Model!B38
G8=Model!B45
G9=Model!B46
G10=Model!B47
G11=Model!B48
G12=Model!B49
G13=Model!B50
G14=Model!B44
G16=Model!B20
G17=output_CurrentAge+1
G19=IF(input_Custom_TermAge>input_MBAA,input_Custom_TermAge,MAX(output_MinAgeTerm,input_PVD))
G20=MAX(input_Custom_TermAge,input_MBAA)
G22=Model!G125
G23=Model!G131
G24=Model!G133
G25=IF(output_DROP_Question,input_ProjectedBenefitAmount,"")
G26=IF(output_DROP_Question,Model!F87,"")
G27=IF(output_DROP_Question,Model!B86,"")
G28=IF(output_DROP_Question,Model!B87,"")
G29=ROUND(output_Global_DROP_COLA/100,4)
G31=Model!E100
G32=Model!E101
G33=IF(Model!E103<0,0,Model!E103)
G34=IF(output_BuyBack_IP_AddOn=0,Model!B50,Model!B51)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
input_Custom_TermAge='Inputs Outputs'!$D$14
input_MBAA='Inputs Outputs'!$D$12
input_ProjectedBenefitAmount='Inputs Outputs'!$D$20
input_PVD='Inputs Outputs'!$D$10
output_BuyBack_IP_AddOn='Inputs Outputs'!$G$33
output_CurrentAge='Inputs Outputs'!$G$16
output_DROP_Question='Inputs Outputs'!$G$22
output_Global_DROP_COLA='Inputs Outputs'!$J$5
output_MinAgeTerm='Inputs Outputs'!$G$17
PVD='Inputs Outputs'!$D$10

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
You must log in or register to reply here.

Similar threads

P
  • Solved
How to parse Json data from webpage to excel sheet ?
Replies
4
Views
2K
ppnar007
P
Smerdis13
  • Question
VBA to Get Output by check relations from Input
Replies
2
Views
151
Smerdis13
Smerdis13
Anigito
  • Question
Get Webpage Title Text by URL Generated in MS Excel
Replies
1
Views
423
Anigito
Anigito
C
  • Question
Loop validation, copy output from same range and paste in different ranges
Replies
1
Views
390
RoryA
RoryA
C
  • Question
Copy value based on dynamic input
Replies
0
Views
322
Carlotta1989
C

Forum statistics

Threads
1,214,918
Messages
6,122,246
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top