you give no clue what you are trying to achieve so not sure if it helps but:

you should never be using big complicated formulae like this in VBA
créate a new tab in your spread sheet
set cell a2 to your formula with cell a1 as input to it and cell a3 as the output you want

test until it works under all circumstances

use vba to put the input value into a1 and read the answer from a3

it looks from what you are trying to do, that not using VBA at all is probably the best option - you are not trying to rewrite Excel from scratch