PDA

View Full Version : Excel formula help



bbuk9999
05-13-2016, 11:26 PM
Does anyone know how to express this formula SUM(1+k, 2+i)?, k & i are not columns or rows, they are variables, these variables are inserted in VBA code. Can we express something like this? Thanks in advance.

SamT
05-14-2016, 09:08 AM
In VBA
X = 1 + 2 + k + i

To use your question in an Excel cell:
In VBA, in a standard Module, Add these two Functions

(Adjust Types As needed)
Public Function Kay(k As Long) As Long
Kay = k
End Function

Public Function Aye(i As Long) As long
Aye =i
End Function

k and i must be public variables in the rest of the code

In the Worksheet Cell Formula:
=1+Kay+2+Aye
Or
=Sum(1,Kay,2,aye)

p45cal
05-15-2016, 12:50 PM
Does anyone know how to express this formula SUM(1+k, 2+i)?, k & i are not columns or rows, they are variables, these variables are inserted in VBA code. Can we express something like this? Thanks in advance.
In vba, if you have a variable such as i you can do something like this:

i = 3
ThisWorkbook.Names.Add "i", i
but be aware you can't use a Name named anything that could be mistaken for a cell address, such as "A1" or "OCD34" so

ThisWorkbook.Names.Add "OCD34", iis out.

Paul_Hossler
05-15-2016, 08:14 PM
Does anyone know how to express this formula SUM(1+k, 2+i)?, k & i are not columns or rows, they are variables, these variables are inserted in VBA code. Can we express something like this? Thanks in advance.

is this to be a formula in a worksheet where K and I are variables in a VBA macro?

i.e. A1 = =SUM(1+K,2+I)

Why not just have the macro calculate 1 + K + 2 + I and put it in A1?


Some more details would be helpful