# Outputting a formula in a cell using VB

1. ## Outputting a formula in a cell using VB

Hi!
I am using VB to maximise a series of functions using the solver Add-In.
Here is a part of my code:

logsum = 0
For l = 1 To n
'Set the initial value of v to 1:
Cells(k + 1, 4) = 1
logsum = logsum + Log(1 + ((Cells(l + 1, 1).Value) ^ 2) / Cells(k + 1,
4).Value)
Next l
loglikelihood = n * Log((1 / (WorksheetFunction.Pi * Cells(k + 1, 4).Value)
^ (1 / 2)) * (Exp(WorksheetFunction.GammaLn((Cells(k + 1, 4).Value + 1) / 2)
- WorksheetFunction.GammaLn(Cells(k + 1, 4).Value / 2)))) - ((Cells(k + 1,
4).Value + 1) / 2) * logsum
Cells(k + 1, 3) = loglikelihood
SolverOk SetCell:="\$k+1\$3", MaxMinVal:=1, ByChange:="\$k+1\$4"
SolverAdd CellRef = "\$k+1\$4", Relation:=3, FormulaText:="0.000001"
SolverSolve UserFinish:=True
Next k

My problem is that I want to maximise the "loglikelihood" with respect
to the value given in cell(k+1,4) but at the moment I am outputting a number
in Cell(k+1,3) not the formula that is used to calculate this number so
Solver does not work. Does anyone have any suggestions how I could output the
formula in the cells instead of the value obtained at the end?

Thanks!

2. Hello Cardiff Maths Student,

If you want to place the formula loglikelihood into Cell(K+1, 3) you have to assign the formula string to the cell's formula property.

Assign Formula to a Cell:
Cell(K+1, 3).Formula = loglikelihood

Sincerely,
Leith Ross

3. ## Re: Outputting a formula in a cell using VB

To get the correct syntax to enter a formula into a cell, turn on the
macro recorder (Tools | Macro > Record new macro...), enter the formula
by hand, and turn off the recorder. XL will give you the necessary
code. You can then generalize this by replacing specific cell

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <E9947CC0-AB1A-46F5-952B-B87632AEF7B5@microsoft.com>,
CardiffMathsStudent@discussions.microsoft.com says...
> Hi!
> I am using VB to maximise a series of functions using the solver Add-In.
> Here is a part of my code:
>
> logsum = 0
> For l = 1 To n
> 'Set the initial value of v to 1:
> Cells(k + 1, 4) = 1
> logsum = logsum + Log(1 + ((Cells(l + 1, 1).Value) ^ 2) / Cells(k + 1,
> 4).Value)
> Next l
> loglikelihood = n * Log((1 / (WorksheetFunction.Pi * Cells(k + 1, 4).Value)
> ^ (1 / 2)) * (Exp(WorksheetFunction.GammaLn((Cells(k + 1, 4).Value + 1) / 2)
> - WorksheetFunction.GammaLn(Cells(k + 1, 4).Value / 2)))) - ((Cells(k + 1,
> 4).Value + 1) / 2) * logsum
> Cells(k + 1, 3) = loglikelihood
> SolverOk SetCell:="\$k+1\$3", MaxMinVal:=1, ByChange:="\$k+1\$4"
> SolverAdd CellRef = "\$k+1\$4", Relation:=3, FormulaText:="0.000001"
> SolverSolve UserFinish:=True
> Next k
>
> My problem is that I want to maximise the "loglikelihood" with respect
> to the value given in cell(k+1,4) but at the moment I am outputting a number
> in Cell(k+1,3) not the formula that is used to calculate this number so
> Solver does not work. Does anyone have any suggestions how I could output the
> formula in the cells instead of the value obtained at the end?
>
> Thanks!
>
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1