0

I am currently removing all live formulas from my Excel sheet and replacing them with a VBA calculation. I have an issue where my fomula from Excel is too long to be used in VBA. I have tried using "union" to split up the fomula but this is not working. It seems this fomula would need to be chanhed to a VBA calculation. Please assist with advice on how to get this to work.

Range("ACA177:BEP188").Value = "=IF(AND(PV_Only_Charge_Batt_T_F=TRUE,OR(Peak_Shaving_Charge_Select=PV_Only_Charge,Peak_Shaving_Charge_Select=PV_OP_Only_Charge),Battery_Function=Battery_Function_Arbitrage,ACA933<>$X$948,ACA933<>$X$954),MIN(MAX(0,ABR806-(ABR834)),Batt_Inv1_QTY*Batt_Inv1_Capacity,(ACA23*(Batt_Inv1_Efficiency/100)),Batt_Inv1_QTY*((Batt_Inv1_DCV*Batt_Inv1_DCA)-Batt_Inv1_0_Load_W)/1000),IF(AND(PV_Only_Charge_Batt_T_F=FALSE,OR(Peak_Shaving_Charge_Select=PV_Only_Charge,Peak_Shaving_Charge_Select=PV_OP_Only_Charge),Battery_Function=Battery_Function_Arbitrage),MIN(MAX(0,ABR806-(ABR834)),Batt_Inv1_QTY*Batt_Inv1_Capacity,(MAX(0,ACA23-ACA89)*(Batt_Inv1_Efficiency/100)),Batt_Inv1_QTY*((Batt_Inv1_DCV*Batt_Inv1_DCA)-Batt_Inv1_0_Load_W)/1000),IF(AND(Battery_Function=Battery_Function_Peak_Shaving,Max_Bat_Charge<ACA23),MIN(((Batt_Nom_V*(Batt_Inv1_QTY*Batt_Inv1_DCA))/1000),(Batt_Total_Cap/1000)-MAX(0,ABR806-(ABR834)),MAX(0,ABR806-(ABR834)),MAX(0,(ACA23-(ACA89-Max_Bat_Charge))*(Batt_Inv1_Efficiency/100)),ACA23,Batt_Inv1_QTY*((Batt_Inv1_DCV*Batt_Inv1_DCA)-Batt_Inv1_0_Load_W)/1000),MIN(Batt_Inv1_QTY*Batt_Inv1_Capacity,ACA23,(Batt_Total_Cap/1000)-MAX(0,ABR806-(ABR834)),MAX(0,ABR806-(ABR834)),MAX(0,(ACA23-ACA89)*(Batt_Inv1_Efficiency/100)),Batt_Inv1_QTY*((Batt_Inv1_DCV*Batt_Inv1_DCA)-Batt_Inv1_0_Load_W)/1000))))"
JSC_B
  • 1
  • 1
  • This is not VBA calculation. This is assigning the same formula back to the sheet again. – GSerg Jan 22 '23 at 23:20
  • How would i convert this to a VBA calculation? – JSC_B Jan 22 '23 at 23:22
  • 1
    That is a different question from the one you are asking in the body of the question, which appears to be about the string literal that is too long for the VBA editor (which this one is). – GSerg Jan 22 '23 at 23:27
  • what are the variables in the code, such as `PV_Only_Charge_Batt_T_F`? – jsotola Jan 22 '23 at 23:28
  • Do the other formulas, that you have successfully converted to VBA, also have the form of `Range("...").Value = "=IF(...)"`? – GSerg Jan 22 '23 at 23:37
  • @jsotola this is from a check box. – JSC_B Jan 22 '23 at 23:42
  • @GSerg yes. This works: Range("ACA111:BEP122").Value = "=IFERROR(IF(AND(OR(Peak_Shaving_Charge_Select=PV_Only_Charge,Peak_Shaving_Charge_Select=PV_OP_Only_Charge),Battery_Function=Battery_Function_Arbitrage),MAX(0,MIN(ACA23-(ACA177/(Batt_Inv1_Efficiency/100)+(Batt_Inv1_0_Load_W/1000)),ACA89)),MAX(0,MIN(ACA23-(ACA177/(Batt_Inv1_Efficiency/100)+(Batt_Inv1_0_Load_W/1000)),ACA89))),MIN(ACA23,ACA89))". – JSC_B Jan 22 '23 at 23:44
  • @JSC_B Again, you are not converting anything to VBA. This code does not do the calculation, it assigns the formula to the sheet, and then the sheet does the calculation. And the formula remains on the sheet, as opposed to in VBA. If that is indeed what you want to happen, then this question was [inappropriately](https://stackoverflow.com/posts/75204584/timeline#history_5fa4339d-4373-4d98-831f-8438e9b83c6b) reopened from the two duplicates I closed it for. – GSerg Jan 22 '23 at 23:46
  • @GSerg I understand this now. I'm relavitely new to VBA but have gotten to a point where all of these calculations need to be converted to VBA. Having the sheet do the calculations has rendered the sheet almost useless as it takes around 30 mins to open the file and 5 mins to recalculate when altering a cell. – JSC_B Jan 22 '23 at 23:54
  • @JSC_B Directly converting the formulas to VBA (when that's [even possible](https://stackoverflow.com/questions/75104146/how-to-translate-worksheet-formula-to-vba-formula#comment132569482_75104146)) will make it even slower. To make it faster, you would need to write code from scratch that implements the original logic that is behind these formulas (which look convoluted because formulas have to use hacks and tricks to simulate loops etc). That code would look nothing like the formulas. – GSerg Jan 23 '23 at 00:00

0 Answers0